Compare DATETIME with DATE

[abenstex]

Hello!

Is there a possibility to compare DATETIME with DATE? It's because I have a table with DATETIME columns but at one point i need to filter the DATETIME entries according to a certain DATE.

Thanks!

 

[r937]

yes, you can do this easily using a range

select foo, bar
from daTable
where datetimecol >= '2005-07-23'
and datetimecol < date_add('2005-07-23'
, interval 1 day)

note that it seems silly to use DATE_ADD on a literal value (you could just as easily code '2005-07-24')

however, that's the structure of the query that you would use if the date is a variable

r937.com | rudy.ca

 

[TonyGroves]

You could also use:[tt]
WHERE datetimecol LIKE '2005-07-23%'
[/tt]
This way, you can be sure that an index will be used if there is one available.

 

[abenstex]

Thanks for your answers. I'll try it as soon as possible.

 

[r937]

the range method does use the index (if one exists)

the LIKE method requires conversion of the internal date format to character string format -- this still might use the index, but it's probably slower

this is probably worth a benchmark, if someone has the time or inclination...

r937.com | rudy.ca

 

[Kozusnik]

I use...
SELECT * FROM table WHERE UNIX_TIMESTAMP(field) BETWEEN UNIX_TIMESTAMP('2005-01-01 00:00:00') AND UNIX_TIMESTAMP('2005-06-30 23:59:59')

Although I'm not sure how universal it is.

Mark