CODEWITHSUNDEEP
sql-server-2012datediffdstdate-difference
GoldBishop
GoldBishop

Reputation: 2861

DateDiff - DST issue

Background

I have a scenario where I am calculating the difference between two dates. While the increment differences are spot on, the final calculation has introduced a 60 minute (1 hour) disparity.

After investigation and "hair pulling" episodes, I have identified that the DST transition in November is the cause for the 60 minute (1 hr) disparity.

Scenario

declare @sdate datetime = '2016-10-29 06:03:00.000PM'
declare @edate datetime = '2016-11-29 11:59:00.000PM'

select 
    DATEDIFF(HOUR, @sdate, @edate),
    DATEDIFF(Minute, @sdate, @edate),
    DATEDIFF(Second, @sdate, @edate)

Question

Ultimately, I need to simply return the number of seconds, or minutes, between the @sdate and @edate variables. I know there will be two times during the year, where the difference value will be off by 60 minutes (1 hour), plus or minus, and want to account for that known disparity within my sql statement.

How can i account for the DST adjustment within a set-based operation, if possible?

Currently, I am getting 44996 as the difference, but that is the un-adjusted time-change difference. I am looking for 45056, which is the adjusted time-change difference.

Upvotes: 3

Views: 2347

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241563

The datetime type in SQL Server has no awareness of time zone or offset from UTC. In order to take DST into account, you need to use the datetimeoffset type.

declare @sdate datetimeoffset = '2016-10-29 06:03:00.000PM -05:00'
declare @edate datetimeoffset = '2016-11-29 11:59:00.000PM -06:00'

select 
    DATEDIFF(Hour, @sdate, @edate),
    DATEDIFF(Minute, @sdate, @edate),
    DATEDIFF(Second, @sdate, @edate)

This will give the results you asked for, taking into account that the offsets for the start and end differed by an hour.

The tricky part is how to determine the offset to begin with. If you are running SQL 2016, or Azure SQL DB, then you could use the AT TIME ZONE function to determine it.

declare @dt datetime = '2016-10-29 06:03:00.000PM'
declare @dto = @dt AT TIME ZONE 'Central Standard Time'

But since you said you are running SQL 2012, you'll have to either write your own functions that understand when DST starts and ends in your time zone, or you can use my SQL Server Time Zone Support package to do that:

declare @dt datetime = '2016-10-29 06:03:00.000PM'
declare @tz varchar = 'America/Chicago'
declare @dto = Tzdb.SwitchZone(Tzdb.LocalToUtc(@dt, @tz, 1, 1), @tz)

Note there is an open item to simplify this to a single function, but the above should work for now.

Upvotes: 3

Related Questions