Daylight Savings vs. SQL

By Greg Enns posted 03-12-2018 10:02 AM

  
I still remember the first day I realized that my SQL reports had datetimes in UTC. Oh...the horror! (Yes, I was newbie with lots to learn.)
It's taken me a few years to get it right, but I finally found a function that works year-round to convert my datetime values to the correct datetime.
The trick is to get it to work both during daylight savings AND outside of. There are lots of solutions on the web--many of which I tried and didn't work year-round. I was hopeful when I tried this one last November, and today I can finally confirm that it works. Hope this helps someone else out there! (many thanks to whomever posted this in a SQL forum somewhere/sometime--You get all the credit!)

/****** Object:  UserDefinedFunction [dbo].[UtcToLocalTime]    Script Date: 3/12/2018 8:38:43 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE FUNCTION [dbo].[udfUtcToLocalTime]

(

    @UTC datetime

)

RETURNS datetime

AS

BEGIN

 

    declare

        @DST datetime,

        @SSM datetime, -- Second Sunday in March

        @FSN datetime,  -- First Sunday in November

             @StandardOffset int = -6  --Timezone adjustment (-6 = Central US)

 

    -- get DST Range

       set @SSM = datename(year,@UTC) + '0314' --start with max possible date

    set @SSM = dateadd(hour,-@StandardOffset,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))

    set @FSN = datename(year,@UTC) + '1107' --start with max possible date

    set @FSN = dateadd(Millisecond,-2,dateadd(hour,-@StandardOffset,dateadd(day,datepart(dw,@FSN)*-1+1,@FSN)))

 

    -- add an hour to @StandardOffset if @UTC is in DST range (summer)

    IF @UTC between @SSM and @FSN

        SET @StandardOffset = @StandardOffset + 1

 

    -- convert to DST

    IF @UTC = '17530101'

             SET @DST = @UTC

       ELSE

             set @DST = dateadd(hour,@StandardOffset,@UTC)

 

    -- return converted datetime

    return @DST

 

END

GO

0 comments
1 view

Permalink