CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetime
alice7
alice7

Reputation: 3880

Query datetime in SQL without time

I'm trying to write a SQL query which should just pick the count with specific date not time.

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

but it is not returning any results.

Upvotes: 6

Views: 13801

Answers (3)

Jeff Ogata
Jeff Ogata

Reputation: 57833

For SQL Server 2008, you should be able to use the date data type:

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

Upvotes: 7

Mārtiņš Briedis
Mārtiņš Briedis

Reputation: 17772

Try (MySQL)

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

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

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

Upvotes: 1

Jason
Jason

Reputation: 15378

If you have a date time field, and you wanted to match a date:

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

MYSQL Date Time ref

Upvotes: 4

Related Questions