SQL query time and time without time

Question

I am trying to write an SQL query that should just select a counter with a specific date, not time.

select count(*) from xyz where time='2010-01-21' 

but it does not return any results.

Answer 1

If you have a date field and you want to combine the date:

select count(*) from xyz where time BETWEEN '2010-01-21' AND '2010-01-22

MYSQL Date Time ref

Answer 2

For SQL Server 2008, you can use the date data type:

 select count(*) from xyz where cast(time as date) = '2010-01-21' 

Answer 3

Try (MySQL)

 SELECT COUNT(*) FROM xyz WHERE DATE(datetime_col) = '2010-01-21' 

in T-SQL (MSSQL) (seemingly ugly, but should work):

 SELECT * FROM xyz WHERE CAST(CONVERT(varchar(8), datetime_col, 112) AS DATETIME) <= '2011-01-21'