CODEWITHSUNDEEP
mysqldatedatetime
Richard Tinkler
Richard Tinkler

Reputation: 1643

Empty result when using mySQL WEEK() to find dates in current week

We are using the following statement to select dates of birth which occur in the current week:

SELECT * FROM tbl_user WHERE WEEK(dob,1) = WEEK(CURDATE(),1) ORDER BY id DESC

Our 'dob' column has the type DATE and contains 1 record where dob is 1972-07-09. However, when we run our query (today is 2014-07-07) we get an empty result.

Any ideas why?? Does WEEK() only work on columns with type DATETIME?

Thanks in advance for an help!

Upvotes: 0

Views: 125

Answers (4)

Sadikhasan
Sadikhasan

Reputation: 18600

SELECT WEEK('1972-07-09',1);  //result= 27
SELECT WEEK('2014-07-07',1);  //result=28

For Example :

SELECT WEEK('2014-01-18',1);  //result=3

Your where condition does not satisfy that why It's return false and Empty Result.

Check Manual here

Upvotes: 1

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

Your definition of week is not the same as MySQLs. For a better and more efficient result you should instead decide on the min and max range you wish to find and select that which allows you to define when your week starts and ends.

SELECT * 
FROM tbl_user 
-- min range == 00:00:00 on the first day of week
WHERE dob >= ADDDATE(CURRENT_DATE, INTERVAL 1-DAYOFWEEK(CURRENT_DATE) DAY)
-- max range == 00:00:00 on the first day of next week
AND dob < ADDDATE(CURRENT_DATE, INTERVAL 8-DAYOFWEEK(CURRENT_DATE) DAY) 
ORDER BY id DESC

The start and end date can be adjusted as needed, but because you aren't applying a function to the dob field you will avoid a table scan.

Upvotes: 0

Giulio De Marco
Giulio De Marco

Reputation: 137

SELECT WEEK('1972-07-09',1);

result: 27

SELECT WEEK('2014-07-07',1);

result: 28

In this case your condition WHERE WEEK(dob,1) = WEEK(CURDATE(),1) is False. That's why you get an empty result.

Upvotes: 0

Jens
Jens

Reputation: 69450

2014-07-07 is week 28 and 1972-07-09 is week 27. 27 is not equals 28 so you get no result.

You can check this with that query:

select week('2014-07-07',1),week('1972-07-09',1) from tbl_user ;

Upvotes: 0

Related Questions