Reputation:
I have write a simple query to check birthday between current date and next 7 days which works fine few days before but now its returning 0 result the reason I've found is its searching between 12-29 to 01-05 so I think that's why its not returning here's the query :
SELECT `U`.`FirstName`, `U`.`LastName`, `U`.`UserGUID`, `U`.`ProfilePicture`
WHERE DATE_FORMAT(U.DOB,'%m-%d')
BETWEEN DATE_FORMAT('2015-12-29 07:08:01','%m-%d')
AND DATE_FORMAT('2016-01-05','%m-%d')
Upvotes: 0
Views: 75
Reputation: 2800
Change query as:
SELECT `U`.`FirstName`, `U`.`LastName`, `U`.`UserGUID`, `U`.`ProfilePicture` WHERE DATE_FORMAT(U.DOB,'%m-%d') BETWEEN date_format(DATE_FORMAT('2015-12-29 07:08:01','%y-%m-%d'),'%m-%d') AND Date_Format(DATE_FORMAT('2016-01-05','%y-%m-%d'),'%m-%d')
Upvotes: 2
Reputation: 180
You can get like below
SELECT `U`.`FirstName`, `U`.`LastName`, `U`.`UserGUID`, `U`.`ProfilePicture` WHERE DAY(cast(U.DOB as datetime)) BETWEEN DAY(cast('2015-12-29 07:08:01' as datetime)) AND DAY(cast('2016-01-05' as datetime)) AND
MONTH(CAST(U.DOB AS DATETIME)) BETWEEN MONTH(CAST('2015-12-29 07:08:01'AS DATETIME)) AND MONTH(CAST('2016-01-05' AS DATETIME))
Upvotes: 0
Reputation: 21691
You can use a string comparison by DATE_FORMAT(U.DOB,'%Y%m')
.
DATE_FORMAT(U.DOB,'%Y%m')
should return dates like: YYYYMM. You would also need to change the format of your variables or use the same method on them.
SELECT `U`.`FirstName`, `U`.`LastName`, `U`.`UserGUID`, `U`.`ProfilePicture`
WHERE DATE_FORMAT(U.DOB,'%Y%m') >= DATE_FORMAT('2015-12-29 07:08:01','%Y%m') AND
DATE_FORMAT(U.DOB,'%Y%m') <= DATE_FORMAT('2016-01-05','%Y%m')
Upvotes: 0