CODEWITHSUNDEEP
sqlsql-serverdatetime
Eric Francis
Eric Francis

Reputation: 24267

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

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?

Upvotes: 518

Views: 2093546

Answers (8)

asharilyas
asharilyas

Reputation: 19

First you need to convert both the dates in same format before conversion

SELECT * 
FROM dbo.March2010 A
WHERE CONVERT(DATE, A.Date) >= 2010-04-01;

Upvotes: 1

Md. Tarikul Islam Soikot
Md. Tarikul Islam Soikot

Reputation: 595

In your query you didn't use single quote around date. That was the problem. However, you can use any of the following query to compare date

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


SELECT * 
FROM dbo.March2010 A
WHERE A.Date >= CAST('2010-04-01' as Date);


SELECT *  
FROM dbo.March2010 A 
WHERE A.Date >= Convert(datetime, '2010-04-01' )

Upvotes: 25

Yee
Yee

Reputation: 1

The date format has no issue with me(Mydate's data type is datetime) :
Where Mydate>'10/25/2021' or Where Mydate>'2021-10-25'
but if add a time, above answers are not working.
Here is what I do:
where cast(Mydate as time)>'22:00:00'
If your query needs a date, please add date such as:
where cast(Mydate as time)>'22:00:00' and Mydate='10/25/2021'

Upvotes: 0

Dan
Dan

Reputation: 101

To sum it all up, the correct answer is :

select * from db where Date >= '20100401'  (Format of date yyyymmdd)

This will avoid any problem with other language systems and will use the index.

Upvotes: 10

dush88c
dush88c

Reputation: 2106

We can use like below as well

SELECT * 
FROM dbo.March2010 A
WHERE CAST(A.Date AS Date) >= '2017-03-22';

SELECT * 
    FROM dbo.March2010 A
    WHERE CAST(A.Date AS Datetime) >= '2017-03-22 06:49:53.840';

Upvotes: 29

Suresh Parmar
Suresh Parmar

Reputation: 31

DateTime start1 = DateTime.Parse(txtDate.Text);

SELECT * 
FROM dbo.March2010 A
WHERE A.Date >= start1;

First convert TexBox into the Datetime then....use that variable into the Query

Upvotes: 3

David
David

Reputation: 73554

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.

Upvotes: 680

SliverNinja - MSFT
SliverNinja - MSFT

Reputation: 31641

Try enclosing your date into a character string.

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

Upvotes: 90

Related Questions