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?
-
35put single quotes around itKevin DiTraglia– Kevin DiTraglia2012-05-17 20:57:24 +00:00Commented May 17, 2012 at 20:57
-
4In addition to quotes, I recommend always using a safe and unambiguous format for date-only string literals. The only one I trust is YYYYMMDD. See my comment to David's answer for the reason why...Aaron Bertrand– Aaron Bertrand2012-05-17 21:06:06 +00:00Commented May 17, 2012 at 21:06
Add a comment
|
8 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.
5 Comments
Aaron Bertrand
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.
David
@Aaron Bertrant - My answer did include that the conversion isn't necessary, starting with "Techically, the pareser might let you get away with <final code sample>. 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.
paperclip
@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.Jacques Mathieu
I think it's clear enough that this is a date, and thus conversion is not necessary.
Try enclosing your date into a character string.
select *
from dbo.March2010 A
where A.Date >= '2010-04-01';
1 Comment
Shaiju T
Adding time will give exact result : where A.Date >= 2014-01-12 12:28:00
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';
1 Comment
pim
Modifying the filter predicate column is not a good idea whatsoever. It prevents index usage almost entirely.
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' )
answered Nov 8, 2021 at 17:24
Md. Tarikul Islam Soikot
6651 gold badge8 silver badges14 bronze badges
1 Comment
Akaisteph7
Convert doesn't seem to work for me but the other two do.
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.
Comments
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
Comments
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;
Comments
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'
