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?
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'
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- How to return only the Date from a SQL Server DateTime datatype
- How to check if a column exists in a SQL Server table
- How to concatenate text from multiple rows into a single text string in SQL Server
- Can I concatenate multiple MySQL rows into one field?
- Exclude a column using SELECT * [except columnA] FROM tableA?
- How can I do an UPDATE statement with JOIN in SQL Server?
- How do I escape a single quote in SQL Server?
- How do I UPDATE from a SELECT in SQL Server?
- Find all tables containing column with specified name - MS SQL Server
- How can I delete using INNER JOIN with SQL Server?
SET LANGUAGE FRENCH. :-) For that date you'll get January 4 instead of April 1. For other dates you might get an error instead.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.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