How do I query for all dates greater than a certain date in SQL Server?

Question

I'm trying:

SELECT *  FROM dbo.March2010 A WHERE A.Date >= 2010-04-01; 

A.Date looks like: 2010-03-04 00:00:00.000

However, this is not working.

Can anyone provide a reference for why?

Answer 1

select *   from dbo.March2010 A  where A.Date >= Convert(datetime, '2010-04-01' ) 

In your query, 2010-4-01 is treated as a mathematical expression, so in essence it read

select *   from dbo.March2010 A  where A.Date >= 2005;  

(2010 minus 4 minus 1 is 2005 Converting it to a proper datetime, and using single quotes will fix this issue.)

Technically, the parser might allow you to get away with

select *   from dbo.March2010 A  where A.Date >= '2010-04-01' 

it will do the conversion for you, but in my opinion it is less readable than explicitly converting to a DateTime for the maintenance programmer that will come after you.