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

sql sql-server datetime
Eric Francis picture Eric Francis · May 17, 2012 · Viewed 1.2M times · Source

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

David picture David · May 17, 2012 
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.

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How to convert DateTime to VarChar

I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?

Read More