MYSQL | Select Query | Convert Date Into Days Of The Week & Apply Where Condition On Those Days

MYSQL | Select Query | Convert Date Into Days Of The Week Like Sunday Monday etc. & Apply Where Condition on Those Days.

QUERY EXAMPLE 1:

SELECT employee_id,hire_date,

CASE

WHEN weekday(hire_date)='0' THEN 'Monday'

WHEN weekday(hire_date)='1' THEN 'Tuesday'

WHEN weekday(hire_date)='2' THEN 'Wednesday'

WHEN weekday(hire_date)='3' THEN 'Thursday'

WHEN weekday(hire_date)='4' THEN 'Friday'

WHEN weekday(hire_date)='5' THEN 'Saturday'

WHEN weekday(hire_date)='6' THEN 'Sunday'

END as actual_day_name

FROM `employees` WHERE  weekday(hire_date)!='6'

QUERY EXAMPLE 2:

SELECT employee_id,hire_date,

CASE

WHEN weekday(hire_date)='0' THEN 'Monday'

WHEN weekday(hire_date)='1' THEN 'Tuesday'

WHEN weekday(hire_date)='2' THEN 'Wednesday'

WHEN weekday(hire_date)='3' THEN 'Thursday'

WHEN weekday(hire_date)='4' THEN 'Friday'

WHEN weekday(hire_date)='5' THEN 'Saturday'

WHEN weekday(hire_date)='6' THEN 'Sunday'

END as actual_day_name

FROM `employees` HAVING actual_day_name!='Monday'

-------------------------------

Temporarily Convert Date into Varchar:-

https://youtu.be/05Gebxap5W8

Subscribe my Channel:-
http://www.youtube.com/user/SanketRooney?sub_confirmation=1

Facebook Page:-
https://www.facebook.com/UnpossibleNS

Twitter Account:-
https://twitter.com/UnpossiblePOG

Blog:-
https://unpossiblepog.blogspot.com/

Website:-
https://unpossiblepog.com/

Gaming Instagram:- (@unpog.gaming)
https://www.instagram.com/unpog.gaming/

-------------------------------

In this video, we shall convert date into days of the week like Sunday Monday etc and apply conditions so that only the rows with specific day will be displayed.

Here, we have employee table with more than 200k rows.

And as you can see, there is a hire_date column.

We shall create a select query so that the first entry should display Monday for 13^th of September 2021.

Make sure that the column that you are using either has a DATE or DATETIME as a data-type.

If you are using varchar, then you can temporarily convert it into date without altering the table structure. Video link is at the top corner & in the description.

First we shall only display, employee_id and hire_date.

There is a function in MYSQL called “weekday()”, inside it’s rounded bracket, use date column’s name.

This function can convert any date into number which is basically a number of the day.

For example, 0 is Monday, 1 is Tuesday etc.

Let’s edit previous query.

We shall be using CASE functionality using CASE, WHEN, THEN and END keywords.

Copy that line and paste it 7 times & add proper days in front of numbers.

Let’s run it.

Now, if you look at this, there is a day name according to the hire date.

Let’s check it by adding 23 September 2021 date which is Thursday.

Now, we shall add where condition on day column.

There are 2 ways to do that.

If you are using a number in condition, then use “WHERE” condition.

However, if you are using day name as a condition, then you have to use the “HAVING” keyword instead and add alias name, which in our case is actual_day_name.

So that is it. Query example link given in the description.

Thanks for watching. Like share and subscribe.