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

User · Answer

select   from tblErrorLog where errorDate BETWEEN  12 20 2008  AND DATEADD DAY  1   12 20 2008

User · Answer

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   lt   errorDate AND errordate  lt   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

User · Answer

WHERE datetime column  gt    20081220 00 00 00 000    AND datetime column  lt   20081221 00 00 00 000

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Datetime in where clause

Examples related to sql

Examples related to sql-server

Examples related to datetime

Examples related to sql-server-2005

Examples related to filter