datetime in where clause

Question

how can i select 12/20/2008 in where clause of sql. it is sql server 2005.

select * from tblErrorLog
where errorDate = '12/20/2008'

Answer 1

You don't say which database you are using but in MS SQL Server it would be

WHERE DateField = {d '2008-12-20'}

If it is a timestamp field then you'll need a range:

WHERE DateField BETWEEN {ts '2008-12-20 00:00:00'} AND {ts '2008-12-20 23:59:59'}

Answer 2

WHERE datetime_column BETWEEN '20081220 00:00:00.000'
                          AND '20081220 23:59:59.997'

Answer 3

select * from tblErrorLog
where errorDate BETWEEN '12/20/2008' AND DATEADD(DAY, 1, '12/20/2008')

Answer 4

First of all, I'd recommend using the ISO-8601 standard format for date/time - it works regardless of the language and regional settings on your SQL Server. ISO-8601 is the YYYYMMDD format - no spaces, no dashes - just the data:

select * from tblErrorLog
where errorDate = '20081220'

Second of all, you need to be aware that SQL Server 2005 DATETIME always includes a time. If you check for exact match with just the date part, you'll get only rows that match with a time of 0:00:00 - nothing else.

You can either use any of the recommend range queries mentioned, or in SQL Server 2008, you could use the DATE only date time - or you could do a check something like:

select * from tblErrorLog
where DAY(errorDate) = 20 AND MONTH(errorDate) = 12 AND YEAR(errorDate) = 2008

Whichever works best for you.

If you need to do this query often, you could either try to normalize the DATETIME to include only the date, or you could add computed columns for DAY, MONTH and YEAR:

ALTER TABLE tblErrorLog
   ADD ErrorDay AS DAY(ErrorDate) PERSISTED
ALTER TABLE tblErrorLog
   ADD ErrorMonth AS MONTH(ErrorDate) PERSISTED
ALTER TABLE tblErrorLog
   ADD ErrorYear AS YEAR(ErrorDate) PERSISTED

and then you could query more easily:

select * from tblErrorLog
where ErrorMonth = 5 AND ErrorYear = 2009

and so forth. Since those fields are computed and PERSISTED, they're always up to date and always current, and since they're peristed, you can even index them if needed.

Answer 5

Use a convert function to get all entries for a particular day.

Select * from tblErrorLog where convert(date,errorDate,101) = '12/20/2008'

See CAST and CONVERT for more info

Answer 6

Assuming we're talking SQL Server DateTime

Note: BETWEEN includes both ends of the range, so technically this pattern will be wrong:

errorDate BETWEEN '12/20/2008' AND '12/21/2008'

My preferred method for a time range like that is:

'20081220' <= errorDate AND errordate < '20081221'

Works with common indexes (range scan, SARGable, functionless) and correctly clips off midnight of the next day, without relying on SQL Server's time granularity (e.g. 23:59:59.997)