The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
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()'