Proper way of getting rows since a date accounting for DST?

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.

Answer

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 datetime or 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 ZONE or 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.