I have a datetime column,
changedate, that I need to use to get rows that have changed in the last week since 1PM. This column is unfortunately in local time (EST). The server is Microsoft SQL Server 2016.
Here is the query I have now:
DECLARE @since datetime = DATEADD(week,-1,SMALLDATETIMEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()), 13, 0)) AT TIME ZONE 'Eastern Standard Time' SELECT * FROM table WHERE changedate AT TIME ZONE 'Eastern Standard Time' >= @since
Since I’m using
AT TIME ZONE for both the column and
@since, will this properly account for DST changes? That’s my understanding per the documentation I’ve found, but I’m not 100% sure if that’s how it works or if I’m missing something.
First, figure out the time you’re wanting to compare against:
-- Get the current date in the given time zone DECLARE @today date = convert(date, sysdatetimeoffset() AT TIME ZONE 'Eastern Standard Time') -- Get the date one week ago DECLARE @dateOneWeekAgo date = DATEADD(week, -1, @today) -- Join the date with the desired time (local to the same time zone) DECLARE @since datetime = convert(datetime, @dateOneWeekAgo) + convert(datetime, timefromparts(1, 0, 0, 0, 0))
Then just compare it:
SELECT * FROM table WHERE changedate >= @since
That assumes your
changedate field is a
datetime2. If it’s a
datetimeoffset, you should first convert the target value to a
datetimeoffset in the same time zone and use that instead:
DECLARE @sinceDTO datetimeoffset = @since AT TIME ZONE 'Eastern Standard Time'
Regarding the approach you gave in the question, there two issues:
getdate()gives the time based on the server’s local time zone. It’s possible that it’s not the same day in Eastern Time.
You should never apply a function (whether an intrinsic like
AT TIME ZONEor something else) against a table field in a where clause, because it makes the query non-sargable. In other words, SQL would have to scan the entire table, rather than using an index. The bigger the table, the slower the query would take.