Query by Date/Time in SQL Server 2005

Question

I have such a simple question, I feel stupid for asking it. I can't believe I'm hitting my head on this.

I have a table called "Orders". "Orders" has a smalldatetime field called "DateOrdered". I need to get all of the "Orders" on a specific date. I have tried the following without any success:

SELECT * FROM Orders WHERE [DateOrdered]=2010-06-01

SELECT * FROM Orders WHERE [DateOrdered]='2010-06-01'

SELECT * FROM Orders WHERE [DateOrdered]=CAST('2010-06-01' AS smalldatetime)

What am I doing wrong? I can't believe I'm even asking this question.

Answer 1

The first query compares the date to the number 2003 (2010 minus 6 minus 1) which converts into the date '1905-06-27'. The second and third query would work for exact date values (i.e. with a 00:00 time component), and are equivalent.

Do you have a time component in your smalldatetime values? In that case you can get the values in an interval:

SELECT * FROM Orders WHERE DateOrdered >= '2010-06-01' 
                       and DateOrdered < '2010-06-02'

Notice the use of >= and < instead of the between keyword, to exclude any records that might have the exact value 2010-06-02.

Answer 2

Like this, also notice the safe ISO date format (YYYYMMDD) which won't blow up if you are in the UK for example

SELECT * FROM Orders WHERE [DateOrdered] >='20100601'
AND [DateOrdered] < '20100602'

This way also will enable you to use an index, if you cast the DateOrdered column an index won't be used

here is an example of what happens when you use YYYY-MM-DD instead of YYYYMMDD

SET LANGUAGE 'us_english'

SELECT CONVERT(DATETIME, '2006-04-06'),  --will be YMD
       CONVERT(DATETIME, '20060406')  --safe format

SET LANGUAGE 'Italian'

SELECT CONVERT(DATETIME, '2006-04-06'),  --will be YDM
       CONVERT(DATETIME, '20060406')  -- safe format