CODEWITHSUNDEEP
phpmysql
user4861457
user4861457

Reputation:

Birthday users between current date and next 7 days not working with between condition

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

Answers (3)

Muhammad Muazzam
Muhammad Muazzam

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

VIPAN SABHERWAL
VIPAN SABHERWAL

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

AddWeb Solution Pvt Ltd
AddWeb Solution Pvt Ltd

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

Related Questions