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...
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?
7 Answers
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.
Try enclosing your date into a character string.
select *
from dbo.March2010 A
where A.Date >= '2010-04-01';
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';
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.
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' )
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
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'
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
