Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

Is Your SQL Server in Daylight Savings Time?

Remember the old ‘Deep Thoughts – by Jack Handy’ bit on Saturday Night Live.  One of those came across the cube wall today.

“Hey – how can we figure out if the SQL Server host is running under Daylight Savings Time (DST) currently”.  The from the cuff consensus of the team was – you can’t from within TSQL.  Well, I don’t accept “you can’t” as an answer.  The fact is there is always a way.  Always.  So – I knew my afternoon had just taken a new course.  Figure out how.  I love a good OK-GO style Rube Goldberg problem.

Pinal Dave is great for questions like this and I found the first part of a potential solution over on his site.  How do you establish what time zone you are currently in.  Turns out you can query the Registry for this information by interrogating the SYSTEM\CurrentControlSet\Control\TimeZoneInformation key with the undocumented xp_regread stored proc.  Thanks Pinal.

Microsoft does not recommend or support xp_regread.  They would undoubtedly rather you get this information via CLR.  But, for our problem at hand – if I were going to use CLR – I would be going a whole different route to obtain DST information.  This would be easy in C#.

So – knowing our current time zone all that is left is to break down the expected offsets from UTC time when NOT in daylight savings time.  For example, here in the Mountain Time Zone that would be an offset of 6 hours.  If our distance from UTC time is actually 7 then we know we are in DST here in the Mountain Time Zone.  So, a quick case statement with all the US time zones and their related offsets plus an else statement of 0 to catch all those non-compliant Arizona types and bam, done.

 

DECLARE @TimeZone VARCHAR(50)
DECLARE @Offset INT

EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 
    'TimeZoneKeyName',
    @TimeZone OUT

SET @Offset = CASE WHEN CHARINDEX('Eastern',  @TimeZone) > 0 THEN 4
                   WHEN CHARINDEX('Central',  @TimeZone) > 0 THEN 5
                   WHEN CHARINDEX('Mountain', @TimeZone) > 0 THEN 6
                   WHEN CHARINDEX('Pacific',  @TimeZone) > 0 THEN 7
                   WHEN CHARINDEX('Alaska',   @TimeZone) > 0 THEN 8
                   WHEN CHARINDEX('Hawaii',   @TimeZone) > 0 THEN 9
                   ELSE 0
              END

SELECT  @TimeZone AS TimeZone ,
        CASE WHEN CAST(
                       DATEDIFF(HH, GETDATE(), GETUTCDATE()) AS INT
                      ) = @Offset
             THEN 1
             ELSE 0
        END AS IsDST

Don’t ever tell me that something can’t be done.  But, before I pat myself on the back too hard, truth is I’m still not TERRIBLY happy with this solution.  It uses undocumented stuff and doesn’t support the globe.  All you brainy types – how would you do this?  Yeah, I know – CLR.  But if you could only use TSQL how would YOU tackle this problem?

Frankly – for a problem like this I’m almost thinking the more Rube Goldberg the better.  For example – last night as I was considering this ( tongue in cheek ):  Have SQL Agent call a C# app that figures it out and then updates a flag file in the OS located in a folder that has FileStream/FileTable enabled.  Then you can query for the existence of a flag in the folder from within TSQL.  Yeah, that should work.

XP_CMDSHELL & POWERSHELL

As fun as Rube Goldberg is … if you would like a more straightforward approach with xp_cmdshell and PowerShell, this also works pretty well. Kudos to @Ms_Alaina for this approach.  The only trick is you have to have xp_cmdshell enabled and have rights to execute.

 

-- enable cmd_shell if not already enabled
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

-- and then run powershell to get DST status of host

EXEC xp_cmdshell 'powershell.exe $(Get-Date).IsDaylightSavingTime()'

 

dst_samp

2 comments on “Is Your SQL Server in Daylight Savings Time?

  1. David S.
    May 16, 2014

    This uses CLR, but at least you don’t have to code it yourself. http://www.sqlsharp.com

    Example:
    SELECT SQL#.Date_IsDaylightSavingTime(‘2013-01-01’)

  2. Pingback: (SFTW) SQL Server Links 30/05/14 - John Sansom

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on May 1, 2014 by in Database Development.
%d bloggers like this: