How do I query for all dates greater than a certain date in SQL Server?
SqlSql ServerDatetimeSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
Try enclosing your date into a character string.
select *
from dbo.March2010 A
where A.Date >= '2010-04-01';
Solution 3 - Sql
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';
Solution 4 - Sql
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.
Solution 5 - Sql
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' )
Solution 6 - Sql
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
Solution 7 - Sql
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'