SQL SELECT WHERE with date and time

Question

I have a SELECT query where I want to find all rows whose DATE and TIME are between 2011-12-11 23:00:00 and 2011-12-12 23:00:00 I try to do it with WHERE but row is empty

WHERE (date >= '2011-12-11' AND time > '23:00:00' )
AND   (date <  '2011-12-12' AND time < '23:00:00' )

Pls, any good suggestion how to change this?

Answer 1

You could use:

SELECT * FROM table WHERE DATETIME(date) BETWEEN '2011-11-11 23:00:00' AND '2011-12-13 23:00:00'

or separate:

SELECT * FROM table WHERE DATETIME(date) > '2011-12-11 23:00:00' AND DATETIME(date) < '2011-12-13 23:00:00'

EDIT:

I am not sure I understand what you are trying to achieve here or how your DB is laid out but assuming date and time are separate fields:

SELECT * FROM table WHERE DATETIME(concat(DATE(date),' ',TIME(time))) BETWEEN '2011-11-11 23:00:00' AND '2011-12-13 23:00:00'

I haven't tested but this may work.