Datetime in where clause

Question

How can I select 12/20/2008 in where clause of sql?

The server is SQL server 2005.

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

Solution 1

WHERE datetime_column >= '20081220 00:00:00.000'
  AND datetime_column < '20081221 00:00:00.000'

Solution 2

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.

Solution 3

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'}

Solution 4

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)

Solution 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

View more solutions

Comments

[Meff]

Doesn't work as described/required in MS SQL Server 2005, I just tried it. Surprisingly it is valid, it just gives you results where the date is as above, and the time is 00:00:00

[J__]

Thanks, the orginal question appeared as if it was just a date field without times. Have added a range for datetime fields.

[J__]

This doesn't work, the dates should be '2008-20-12' if you're using this approach. (yyyy-dd-mm). This would be the correct format if using the {ts} identifiers.

[onupdatecascade]

I seem to have a couple of down votes on this, and I wonder why. If you don't think this solution works, please educate me!

[LukeH]

@_J_: The yyyy-MM-dd format does work in most circumstances, but not all of them. @onupdatecascade: You're right. I've updated my answer to use a safe-in-all-circumstances format.

[Keith]

@Meff This syntax works for any database that supports ODBC scalar functions, not just MS SQL. However all it does is cast the constant's type from string to date.

[htm11h]

Well I just looked this up sometime after originally posted, and have noted that it does not included the time, noting also that OP did not either, yet you reference SQL granularity. I did up vote though because I use this format as well.

[James Z]

This is not a good way to do it because indexes cannot be used if convert is used for the column

[James Z]

Instead of ,997 that might not be future proof, you should really use datetime_column >= '20081220' and datetime_column < '20081221' -- this way there's also no issues with the date format, YYYYMMDD works

[Mauricio Crispim]

after more than 10 yers, you answer help me soo much. Thank you!!!