tags:

views:

193

answers:

2

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.

+4  A: 
select * from table where mydatetime < dateadd(hh, -1, getdate())
Jonas Lincoln
Be careful with that because mydatetime is a datetimeoffset which may not be in the same time zone as the server. If you use SYSDATETIMEOFFSET (or optionally, use GETUTCDATE() and use SWITCHOFFSET on mydatetime to convert it to UTC) you will be comparing apples to apples.
Josh Einstein
That's correct.
Jonas Lincoln
+1  A: 

WHERE mydatetime < DATEADD(hour, -1, SYSDATETIMEOFFSET())

Josh Einstein