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 13th
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.
