491

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?

cнŝdk
  • 31,391
  • 7
  • 56
  • 78
Eric Francis
  • 23,039
  • 31
  • 88
  • 122

8 Answers8

651
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.

Malachi
  • 3,205
  • 4
  • 29
  • 46
David
  • 72,686
  • 18
  • 132
  • 173
  • 44
    The explicit convert is not necessary. Also I highly recommend using YYYYMMDD instead of YYYY-MM-DD. Why? Well, try your code with `SET LANGUAGE FRENCH`. :-) For that date you'll get January 4 instead of April 1. For other dates you might get an error instead. – Aaron Bertrand May 17 '12 at 21:05
  • 6
    @Aaron Bertrant - My answer did include that the conversion isn't necessary, starting with "Techically, the pareser might let you get away with . I just find it more readable, because it's strikingly obvious that this is a date-time. Too many Database Systems store date values in a varchar field, but you're right about the format. Normally, when using conversion I add in the format specifier as well, but I was doing my sample off the top of my head. – David May 17 '12 at 21:09
  • 4
    @AaronBertrand, I had to use your suggestion in conjuction with the above answer: `CONVERT(datetime, '20100401 10:01:01')` - passing 2010-04-01 works in SQL Server Management Studio but not when sending the SQL statement via PHP/MSSQL. – paperclip Aug 30 '13 at 15:21
  • 1
    I think it's clear enough that this is a date, and thus conversion is not necessary. – Jacques Mathieu May 22 '18 at 16:37
  • I wasn't able to use the `Convert(datetime, '2010-04-01' )` in BigQuery . If anyone looking to execute greater than date function in BigQuery , you can follow the answer mentioned in the link below [link](https://stackoverflow.com/a/51270266) – Raxy Jun 11 '21 at 19:15
83

Try enclosing your date into a character string.

 select * 
 from dbo.March2010 A
 where A.Date >= '2010-04-01';
SliverNinja - MSFT
  • 31,051
  • 11
  • 110
  • 173
27

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';
dush88c
  • 1,918
  • 1
  • 27
  • 34
  • 3
    Modifying the filter predicate column is not a good idea whatsoever. It prevents index usage almost entirely. – pim May 31 '17 at 17:09
19

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' )
Md. Tarikul Islam Soikot
  • 455
  • 1
  • 4
  • 14
10

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.

whoami - fakeFaceTrueSoul
  • 17,086
  • 6
  • 32
  • 46
Dan
  • 101
  • 1
  • 2
3
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

Suresh Parmar
  • 31
  • 1
0

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'

ahuemmer
  • 1,653
  • 9
  • 22
  • 29
Yee
  • 1
0

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;
asharilyas
  • 9
  • 1