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

Question

I am trying:

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

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

But, this is not working.

Can anybody give a reference for why?

Answer 1

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

In this query, 2010-4-01 is treated as a mathematical expression. Therefore, in essence, it reads

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

(2010 minus 4 minus 1 is 2005, converting this to a proper datetime and adding single quotes will solve this problem.)

So the parser here might allow you to get away with

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

It will be doing the conversion for you, but in my view, it is less readable than explicitly converting to a datetime for the maintenance programmer that will come after you. 

Want to be an expert in SQL programming? Join Intellipaat's SQL Certification course and learn in depth.

Answer 2

For querying the dates greater than a certain date, you need to use Date comparison using the “>” operator. 

For example, if you have an orders table where you aim to extract all the records greater than the date “2023-01-01”, you need to execute the below query -

Select * from orders 

Where orderDate > ‘2023-01-01”.

This query will filter the dates greater than “2023-01-01”.

Answer 3

The challenge you encounter while constructing the SQL statement lies in the manner you specify the date within the conditions of the WHERE clause.As an illustration, while writing 2010-04-01 in SQL Server, it takes the expression mathematically as [(2010-1) -4] and does not consider it as a date.

To fix this, one has to make use of the date string in single quotes.

SELECT * 

FROM dbo.March2010 A

WHERE A.Date >= '2010-04-01';