CODEWITHSUNDEEP
mysqlsqldatebetween
user1919077
user1919077

Reputation: 31

Between mysql date_format not working

I have this query

SELECT * 
FROM `users_profile` 
WHERE DATE_FORMAT(dob,'%d-%m-%Y') BETWEEN '05-03-1996' AND '05-03-1915'

which should return two results which both have these dates in the dob column

08-02-1996
14-02-1996

But it dosen't return anthing!! What am I doing wrong!!??

Upvotes: 0

Views: 1144

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Why would you take a perfectly good date and convert it to a (bad) string for comparison?

Do the comparison as dates and put the constants in the right order:

SELECT * 
FROM `users_profile` 
WHERE dob BETWEEN date('1915-03-05') and date('1996-03-05');

Also note that I changed the date format for the date constants to YYYY-MM-DD. This is the ISO standard format for dates. (Despite that), it is a really good idea to use.

I am assuming that dob really is a date, because that is what the function date_format() takes for its first argument.

Upvotes: 1

Related Questions