1

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.

flashbang
  • 142
  • 1
  • 11

1 Answers1

1

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.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Wow, thank you so much, and thanks for the tips (my lack of SQL knowledge is showing)! So if I understand this correctly, declaring `@since` as you described and using that as the criteria for `changedate` (which is a `datetime` in EDT or EST) would properly account for any DST changes in that field? – flashbang Jul 17 '20 at 16:17
  • Assuming the data in the field is indeed Eastern Time as observed in US and Canada - that is EST (UTC-5) in the winter, EDT (UTC-4) in the summer - then *yes*. However, if actually the data in the field is UTC-5 all year for some reason, then change the first line to: `DECLARE @today date = convert(date, switchoffset(sysdatetimeoffset(), '-05:00'))`. See [the `switchoffset` docs](https://learn.microsoft.com/sql/t-sql/functions/switchoffset-transact-sql). – Matt Johnson-Pint Jul 17 '20 at 20:10