The Pain of DST and the Lessened Pain with AT TIME ZONE

Bert Wagner shows how you can use AT TIME ZONE as of SQL Server 2016 to make dealing with Daylight Savings Time a little less painful:

The fallacy above is that I said our two datetime2’s are in UTC, but SQL Server doesn’t actually know this. The datetime2 (and datetime) datatype doesn’t allow for time zone offsets so SQL Server really doesn’t know what time zone the data is in.

Using AT TIME ZONE on a datetime2 without offset information causes SQL Server to “…[assume] that [the datetime] is in the target time zone”. That explains why the two datetime2s above, intended to be in UTC, are actually seen as Eastern Daylight Time by SQL Server.

Read the whole thing. Dates and times are a lot more difficult than they first appear. And then they turn out to be a lot more difficult than that.

Related Posts

Getting Prior Year’s Year-To-Date with DAX

Kasper de Jonge takes a look at how to calculate a prior year’s year-to-date over the same period as the current year: Well maybe.. what happens here is that the DAX engine took the whole date range we have in context and shifts it back 12 months. This means for year 2019 it will use […]

Read More

SARGability and Date Functions

Erik Darling shows why you don’t want to use YEAR() or MONTH() in the WHERE clause when querying a large table: If you’ve been query tuning for a while, you probably know about SARGability, and that wrapping columns in functions is generally a bad idea. But just like there are slightly different rules for CAST and […]

Read More

Categories

March 2019
MTWTFSS
« Feb Apr »
 123
45678910
11121314151617
18192021222324
25262728293031