Menu
I know this should be easy and I do it no probelms in mySQL and Postgresql but I'm struggling with SQL Server. I want to select rows with a datetimeoffset field that's over an hour old.
select * from table where mydatetime < getdate() - 1 hour
I've tried dateadd and datediff but can't get it right.
525
Note: DATEADD and DATEDIFF SQL function can be used in the SELECT, WHERE, HAVING, GROUP BY and ORDER BY clauses.
This depends on whether or not you need to include a time zone offset. If you need to include a time zone offset, then you'll need to use datetimeoffset. If not, then use datetime2, as you'll save storage space and eliminate any potential issues with having a (potentially wrong) time zone offset in your data.
We use system function now() to get the latest datetime value, and INTERVAL clause to calculate a date 10 minutes in the past.
A time zone offset specifies the zone offset from UTC for a time or datetime value. The time zone offset can be represented as [+|-] hh:mm: hh is two digits that range from 00 to 14 and represent the number of hours in the time zone offset.
WHERE mydatetime < DATEADD(hour, -1, SYSDATETIMEOFFSET())
For more see: DATEADD (Transact-SQL)
175
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
