Checking the New DST 2007 from a DBA Perspective

• Download source code - 10.35 KB
• Download text - 2.78 KB

Preamble

As a result of the Energy Policy Act that was passed in August 2005 by the United States Congress, there is a change in the daylight saving time starting from 2007 as follows:

This document shows you how to check if your Microsoft SQL Server 2005 is ready for the new DST 2007.

Technical Details

The managed assembly ConvertTime.dll contains 1 stored procedure (ufnDisplayUTC) and 2 functions (ufnToLTime, and ufnToUTime) as follows:

• ufnDisplayUTC displays information about the current DST. For example:

  Timezone: . . . . . . Eastern Standard Time
  New DST for 2007: . . 2007-03-11 02:00 to 2007-11-04 02:00 (01:00:00)
  Current time: . . . . 1/31/2007 11:30:32 AM
  Universal time: . . . 1/31/2007 4:30:32 PM
  UTC offset: . . . . . -05:00:00
  Daylight saving? . . No

• ufnToUTime(@strDateTime) returns the universal date and time of a given local date and time. For example:
  SQL

  select dbo.ufnToUTime('2007-03-11 02:00:00.000')
  --3/11/2007 7:00:00 AM

• ufnToLTime(@strDateTime) returns the local date and time of a given universal date and time. For example:
  SQL

  select dbo.ufnToLTime('2007-03-11 07:00:00.000')
  --3/11/2007 3:00:00 AM

Implementation

Execute the following script within the Microsoft SQL Server Management Studio for a database such as AdventureWorks:

CREATE ASSEMBLY [ConvertTime]
FROM 'ConvertTime.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION [dbo].[ufnToLTime](@strDateTime [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [ConvertTime].[MCSCS.SQL2005.UserDefinedFunctions].[ufnToLTime]
GO
CREATE FUNCTION [dbo].[ufnToUTime](@strDateTime [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [ConvertTime].[MCSCS.SQL2005.UserDefinedFunctions].[ufnToUTime]
GO
CREATE PROCEDURE [dbo].[ufnDisplayDST]
AS
EXTERNAL NAME [ConvertTime].[MCSCS.SQL2005.UserDefinedFunctions].[ufnDisplayDST]
GO

Testing

If your server has not been patched with the new DST 2007, you will observe the following result:

select dbo.ufnToUTime('2007-03-11 02:00:00.000')
--3/11/2007 7:00:00 AM

select dbo.ufnToUTime('2007-03-11 03:00:00.000')
--3/11/2007 8:00:00 AM

However, if your server has been patched with the new DST 2007, you will observe the following result:

select dbo.ufnToUTime('2007-03-11 02:00:00.000')
--3/11/2007 7:00:00 AM

select dbo.ufnToUTime('2007-03-11 03:00:00.000')
--3/11/2007 7:00:00 AM

Conclusion

For more information on how to patch your server, please check out the following URL: http://www.microsoft.com/windows/timezone/dst2007.mspx.

History

• 31^st January, 2007: Initial post