finding records between dates problem

[bigjerms]

Here is my query.

SELECT * FROM callhistory
WHERE date_format(callhistory.TimeStamp, '%m/%d/%Y') between '09/15/2004' and '01/08/2006'

This returns nothing.

If I change it to

SELECT * FROM callhistory
WHERE date_format(callhistory.TimeStamp, '%m/%d/%Y') between '09/15/2004' and '12/08/2006'

It works fine.

What am I doing wrong?

 

[r937]

what you're doing wrong is attempting to use varchar strings as dates

you don't have to format a date, mysql knows it's a date (assuming, of course, that you have actually declared it as a DATE datatype)

try this --

SELECT * FROM callhistory
WHERE TimeStamp between '2004-09-15' and '2006-12-08'


r937.com | rudy.ca

 

[bigjerms]

I figured it out. You are right, but I messed up originally thinking it was having problems with comparing dates that were not padded in the month and day to 2 characters.

I now realize that mysql expects the year first when comparing I was feeding it dates with the month first which would mess up everything. This works ok until you you have two different years.