How to select date from datetime column?

Question

I have a column, which is datetime type, the value is like this:

2009-10-20 10:00:00

I want to do a select, my query:

SELECT * FROM 
data 
WHERE datetime = '2009-10-20' 
ORDER BY datetime DESC

Should I do this:

SELECT * FROM 
data 
WHERE datetime BETWEEN('2009-10-20 00:00:00' AND '2009-10-20 23:59:59'
ORDER BY datetime DESC

But it returns 0 result. Please help

Answer 1

Where DATE(datetime) = '2009-10-20'

or try this also and see whether it works on not. Does this helps WHERE datetime LIKE '2009-10-20%' You need to check whethere it works on not.

Answer 2

Hi, You may convert the date before comparison like:

WHERE Convert(varchar(10), datetime, 101) = '10-20-2009'

Check the Convert TSQL function for more details with DateTime data type.

Answer 3

You can use:
DATEDIFF ( day , startdate , enddate ) = 0

Or:
DATEPART( day, startdate ) = DATEPART(day, enddate) AND DATEPART( month, startdate ) = DATEPART(month, enddate) AND DATEPART( year, startdate ) = DATEPART(year, enddate)

Or:
CONVERT(DATETIME,CONVERT(VARCHAR(12), startdate, 105)) = CONVERT(DATETIME,CONVERT(VARCHAR(12), enddate, 105))

Answer 4

The database performance shall be good only for the query "SELECT * FROM data WHERE datetime BETWEEN('2009-10-20 00:00:00' AND '2009-10-20 23:59:59' ORDER BY datetime DESC" with a index on datetime column. Other things all have database performance degradation

-chandru www.mafiree.com