Weekday(date) compare with current date weekday

Question

SELECT 
  * 
FROM
  (`users`) 
  JOIN `artistprofiles` AS art 
    ON `art`.`user_id` = `users`.`id` 
WHERE WEEKDAY(users.date_created) = 'WEEKDAY(WEEKDAY(NOW()))' 
  AND `users`.`status_enum` = 'aproved' 
  AND `users`.`status` = 1 
ORDER BY `users`.`email_process` DESC

what is the problem with this query . it gives me wrong result

try this `WEEKDAY(users.date_created) = WEEKDAY(NOW())` without single quotes — M Khalid Junaid, Mar 26 '14 at 08:14

score 1 · Answer 1 · edited May 23 '17 at 12:11

1

Try like this:

SELECT * FROM (`users`) JOIN `artistprofiles` AS art ON `art`.`user_id` = `users`.`id` WHERE WEEKDAY(users.date_created) = WEEKDAY(NOW()) AND `users`.`status_enum` = 'aproved' AND `users`.`status` = 1 ORDER BY `users`.`email_process` desc

The ' make it a string. So you don't get the weekday.

You might want to read this: When to use single quotes, double quotes, and backticks in MySQL

edited May 23 '17 at 12:11

Community

1
1

answered Mar 26 '14 at 08:16

fancyPants

50,732
33
89
96

i am writing it in codeigniter which automatically puts '' on it how can i remove it – Tariq Mar 26 '14 at 10:34

score 1 · Accepted Answer · answered Mar 26 '14 at 08:27

This is wrong:

WHERE WEEKDAY(users.date_created) = 'WEEKDAY(WEEKDAY(NOW()))'

Example:

select 
    @wd:=WEEKDAY(curdate()) wd, 
    @str:='WEEKDAY(WEEKDAY(NOW()))' str, 
    @wd=@str,
    @wd=WEEKDAY( now() ) ;

Results:

+------+-------------------------+----------+----------------------+
| wd   | str                     | @wd=@str | @wd=WEEKDAY( now() ) |
+------+-------------------------+----------+----------------------+
|    2 | WEEKDAY(WEEKDAY(NOW())) |        0 |                    1 |
+------+-------------------------+----------+----------------------+

In your query you are comparing week day value with a literal string 'WEEKDAY(WEEKDAY(NOW()))'. As they do not match, it returns a false and hence you see wrong results.

To check if the week day of the column with week day of now, then

Change:

WHERE WEEKDAY(users.date_created) = 'WEEKDAY(WEEKDAY(NOW()))'

To:

WHERE WEEKDAY(users.date_created) = WEEKDAY( NOW() )

Weekday(date) compare with current date weekday

2 Answers2