Our script dstcheck_global.ps1 takes only one parameter – current Eastern Standard Time (EST). This is very convenient for the companies that are headquartered in the east coast, for example, NYC. However, our script can be used for any company as long as you know the current EST time. To compare the local time you get from any SQL Server hosts with this standard time, we need to know four things.

1.  The normal time difference between the time zone of the SQL Server and the Universal Time (UT). Because the SQL Server data engine gets its time from the operating system, the bias value under the registry key HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation is the normal time difference in minutes.

2.  The DST observance or non-observance at the time zone. We can determine this based on the StandardName value under the registry key HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation. This value has the name of the time zone. If the time zone observes DST, then the actual time difference is the normal time difference (bias) minus 60 minutes.

3.  The normal time difference between the EST and the UT, which is 5 hours (300 minutes).

4.  The DST observance or non-observance at the EST. We can compare the given EST time with the DST starting and ending dates in the EST. In our example of year 2007, at 1:59am EST on 3/11/07, clocks sprang forward to 3am. At 1:59am EST on 11/4, clocks fell back to 1am. Therefore, if the given EST time was after 3am on 3/11/07 and before 1am on 11/4/07, then we assumed the EST observed DST. However, if the given EST time was after 1am but before 2am on 11/4/07, then it could be either that clocks in EST were going to fall back to the normal time or they had already fallen back. Our script then needed to ask a question "Does DST end in EST?". Depending on the user's response, we could decide if the EST observed DST or not. If the EST observed DST, then the actual time difference between the EST and the UT is 240 minutes. If not, then it is 300 minutes.

Once we know these four things, we can calculate the accuracy of the local time of a SQL Server.

If the local time is correct, then:

UT = EST time + EST time difference  
 = local time + local time difference

In our script, the given EST time is stored in a variable $baseEST, and the EST time difference in $ESToffset. The local time is retrieved from the SQL Server using the getdate() function. The actual local time difference is stored in a variable $actualbias. Therefore,

UT = $baseEST + $ESToffset = getdate() + $actualbias

However, if the local time is off, then the inaccuracy from the correct time is:

$diffminutes = $baseEST + $ESToffset – getdate() - $actualbias

We also need to include the execution duration of the script because going through hundreds of SQL Server instances can take tens of minutes. The EST time is given at the beginning of the execution and is no longer accurate after the script runs for, say, 20 minutes. The execution duration is stored in a variable $elaspedminutes. Therefore,

$diffminutes = $baseEST + $ESToffset + $elaspedminutes – getdate() - $actualbias 

For example, when $baseEST was ‘2007-10-27 20:00’, EST observed DST and $ESToffset was 240 minutes. We got a local time ‘2007-10-27 19:10’ from a Chicago SQL Server instance after the script ran for 20 minutes. The actual time difference on the Chicago SQL Server host was 300 minutes because CST observed DST too. Therefore, the inaccuracy of the Chicago time was:

$diffminutes = DateDiff(‘2007-10-27 20:00’, ‘2007-10-27 19:10’) + 240 + 20 – 300 
                  = 10

The Chicago time was 10 minutes behind the correct time.

Our script allows a 30-minute inaccuracy, i.e., if the local time is less than 30 minutes behind or ahead of the EST time, then it is considered ok. Therefore, if the absolute value of $diffminutes is less than 30, then the SQL Server passed the check. Otherwise, the server is reported as an exception. If you need a better accuracy, you can simply change this “if” statement in the script.

if (( $diffminutes -gt 30 ) -or ( $diffminutes -lt -30 ))

Our script gets a list of SQL Server instances from a serverfile.txt file. We put the file and the script dstcheck_global.ps1 in the same directory, for example, C:\Users\Yan\Documents\DST.

PS C:\> Set-Location C:\Users\Yan\Documents\DST
PS C:\Users\Yan\Documents\DST>  Get-ChildItem

    Directory: Microsoft.PowerShell.Core\FileSystem::C:\Users\Yan\Documents\DST

    Mode LastWriteTime Length Name
    ---- ------------- ------ ----
    -a--- 2/10/2008  11:00 AM 10587 dstcheck_global.ps1
    -a--- 2/10/2008  11:13 AM 6212 serverfile.txt

A sample of the server file is shown here.

PS C:\Users\Yan\Documents\DST> Get-Content serverfile.txt
lnhost01\LNINSTANCE1,7001
lnhost02\LNINSTANCE2,7001
nyhost01\NYINSTANCE1,7002
nyhost02\NYINSTANCE2,7001
nyhost03\NYINSTANCE3,7002
nyhost04,1433
hkhost01\HKINSTANCE1,7001
...

To run the script, type:

PS C:\Users\Yan\Documents\DST> dstcheck_global.ps1 -baseEST '2007-10-27 20:05'
 
    Directory: Microsoft.PowerShell.Core\FileSystem::C:\Users\Yan\Documents\DST
 
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---         2/10/2008  11:21 AM          0 dstcheck.exception.1696

After the script executes, look at the exception file dstcheck.exception.1696 for any exceptions. If there are exceptions, then the file looks like this:

PS C:\Users\Yan\Documents\DST> Get-Content dstcheck.exception.1696
Cannot connect with lnhost02\LNINSTANCE2,7001
nyhost04,1433 is 44 minute(s) behind
...

Please note that the script is intended to verify time before or after DST events. It should not be executed while a DST event is occurring. Otherwise, some servers may be falsely reported as an exception.

For this year 2008, you just need to change the values of these five variables to reflect the DST changing dates.

$ESTDSTStart='2008-03-09 03:00'
$ESTDSTEnd='2008-11-02 01:00'
$ESTDSTEndChange='2008-11-02 02:00'
$GMTDSTStart='2008-03-29 21:00'  
$GMTDSTEnd='2008-10-25 21:00'

Conclusion

This article has presented a solution to verify time on SQL Server globally after DST events. The accompanying script can be reused every year with minor changes.