How to Query for All Dates Greater Than a Certain Date in SQL Server

How do I query for all dates greater than a certain date in SQL Server?

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.

Why selecting a value greater than a certain date also return the matched date?

Based on the discussion, the problem I encountered was caused by fractions of a second or microsecond precisions in the Postgres timestamp.

My query is equivalent to where "createdAt" > '2021-10-18 13:27:01.000'. And if I have data which createdAt value is '2021-10-18 13:27:01.230' then of course it will be included in the query result.

Solutions:

1. Ignore the microsecond by using date_trunc (more):

select * from contents where date_trunc('second', "createdAt") > '2021-10-18 13:27:01'

2. Add 1 second.

If you want to select all data after 2021-10-18 13:27:01 then just add 1 second to it:

select * from contents where "createdAt" => '2021-10-18 13:27:02';

It will compare the "createdAt" value to => '2021-10-18 13:27:02.000' which is > '2021-10-18 13:27:01'

I recommend the first solution. The second solution is prone to mismatch the entry, in my opinion. Because we need to process the timestamp before actually executing it. But it still does the trick.

SQL Server Datetime newer than specific date

For specific dates:

SELECT * FROM mytable WHERE mydatefield > '2014-07-14'

For specific dates and times:

SELECT * FROM mytable WHERE mydatefield > '2014-07-14 09:18'

Put the dates/times you want in the query.

MSSQL: Date is greater than specific date or is null

If you want isnull() you can do it like this:

WHERE l.[Start Date] > '07/01/2018' and isnull(l.[End Date], '11/1/2018') > '10/1/2018'

If l.[End Date] is null then

isnull(l.[End Date], '11/1/2018')

will return

'11/1/2018'

and the condition

isnull(l.[End Date], '11/1/2018') > '10/1/2018'

will return true.

Of course you can use instead any date like '1/1/2099'

SQL statement to find older data between a certain date based on date column

You can do

SELECT Email_Address
FROM User_Email_Address
WHERE AccDate BETWEEN DATEADD(DAY, -60, GETDATE()) AND DATEADD(DAY, -30, GETDATE()); 

And drop the check Email_Address = Email_Address as the e-mail address is always equal to itself.

SQL - Greater than date query not working

Use instead of ([Last Update Date] >= '01/01/2016') the following:

YEAR( [Last Update Date]) = 2016

to get all the records that updated in 2016

Impala SQL to slect all records greater than a date - Date column is in milliseconds

Try this query:

SELECT  last_modified_date, 
        FROM_UNIXTIME(last_modified_date), "yyyy-MM-dd HH:mm:ss.SSS") as "Completed_Date" 
FROM helix_access.chg_infrastructure_change 
WHERE FROM_UNIXTIME(last_modified_date), "yyyy-MM-dd HH:mm:ss.SSS") >= '2022-05-09 00:00:00.000'
limit 100;

---

Related Topics