Advanced Search

Get daylight savings time

/ Published in: SQL
Save to your folder(s)
Many of us have come across cases where we need to convert a time (such as UTC) to local time, but what about those parts of the world where daylight savings time is observed?



This function will return the daylight savings date/time for a given year in accordance with historical records: http://www.energy.ca.gov/daylightsaving.html#chart



This way, no lookup tables are needed. All you have to know is what timezone you are converting from and to, because as long as you have the date when daylight savings begins/ends, then it should be fairly easy to write some case logic to convert the date. This script also contains an example of this function's use by converting UTC to PST.



Caveat: The function can return undesirable results if the SET DATEFIRST statement is set to anything other than Sunday.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. IF NOT object_id('dbo.udf_GetDaylightSavingsTime') IS NULL
  2.     DROP FUNCTION dbo.udf_GetDaylightSavingsTime
  3. GO
  4.  
  5. CREATE FUNCTION [dbo].udf_GetDaylightSavingsTime(@YEAR INT, @extent VARCHAR(255))
  6. RETURNS datetime
  7. /**********************************************************************
  8. PROCEDURE:      udf_GetDaylightSavingsTime
  9. PARAMETERS:     @year: the year to return the daylight savings time begin
  10.                 date
  11.                 
  12.                 @extent ('Begin' or 'End'): indicates whether the function
  13.                 returns the begin or end date of daylight savings time
  14.  
  15. APPLICATION:	System Support
  16. PURPOSE:		This procedure will return the date of daylight savings
  17.                 time according to the current federal schedule, which is 
  18.                 currently the second Sunday in March: http://www.energy.ca.gov/daylightsaving.html#chart
  19.  
  20. NOTES:          This procedure will also return the begin date for the previous
  21.                 schedule which ended in 2006, which was the first Sunday of 
  22.                 April
  23.  
  24. EXAMPLES:       print dbo.udf_GetDaylightSavingsTime(2003, 'Begin')
  25.                 print dbo.udf_GetDaylightSavingsTime(2003, 'End')                
  26.                 
  27.                 print dbo.udf_GetDaylightSavingsTime(2009, 'Begin')
  28.                 print dbo.udf_GetDaylightSavingsTime(2009, 'end')
  29.                 
  30. MODIFIED DATE   AUTHOR              DESCRIPTION
  31. --------------  --------------      -------------------------------
  32.  
  33. **********************************************************************/
  34. AS
  35. BEGIN -- function
  36.  
  37. DECLARE @dateTime datetime
  38. ,       @ErrorMessage VARCHAR(1000)
  39. ,       @ProcName     VARCHAR(128)
  40.  
  41. SET @ProcName = object_name(@@procid)
  42.  
  43. IF @extent NOT IN('Begin', 'End')
  44. BEGIN
  45. 	SET @dateTime = 1/0
  46. END
  47.  
  48. SET @dateTime = CASE @extent
  49.                     WHEN 'Begin' THEN
  50.                         CASE 
  51.                             --latest daylight savings time 
  52.                             WHEN @YEAR >= 2007 THEN CAST('3/8/' + CAST(@YEAR AS VARCHAR(4)) AS datetime)
  53.                             --old daylight savings time prior to 2007
  54.                             WHEN @YEAR <= 2006 THEN CAST('4/1/' + CAST(@YEAR AS VARCHAR(4)) AS datetime)
  55.                         END
  56.                     WHEN 'End' THEN
  57.                         CASE 
  58.                             --latest daylight savings time 
  59.                             WHEN @YEAR >= 2007 THEN CAST('11/1/' + CAST(@YEAR AS VARCHAR(4)) AS datetime)
  60.                             --old daylight savings time prior to 2007
  61.                             WHEN @YEAR <= 2006 THEN CAST('10/31/' + CAST(@YEAR AS VARCHAR(4)) AS datetime)
  62.                         END
  63.                 END
  64.  
  65. SET @dateTime = CASE
  66.                     WHEN @extent = 'End' AND @YEAR <= 2006
  67.                         THEN DATEADD(DAY,1-DATEPART(weekday,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@dateTime)+1, 0))),dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@dateTime)+1, 0)))
  68.                 ELSE
  69.                     CASE WHEN datepart(dw, @dateTime) = 1
  70.                         THEN @dateTime
  71.                         ELSE dateadd(dd, 8 - datepart(dw, @dateTime), @dateTime)
  72.                     END
  73.                 END
  74.  
  75. -- daylight savings time begins at 2 am
  76. RETURN CONVERT(VARCHAR(10), @dateTime, 101) + ' 02:00:00'
  77. END -- function
  78. GO
  79.  
  80.  
  81.  
  82. DECLARE @TestyWesty TABLE (UTCDate datetime)
  83.  
  84. --Test DST Begin date
  85. INSERT INTO @TestyWesty VALUES('03/08/2009 09:59:59')
  86. INSERT INTO @TestyWesty VALUES('03/08/2009 10:00:00')
  87. INSERT INTO @TestyWesty VALUES('03/08/2009 10:00:01')
  88.  
  89. --Test DST End date
  90. INSERT INTO @TestyWesty VALUES('11/01/2009 08:59:59')
  91. INSERT INTO @TestyWesty VALUES('11/01/2009 09:00:00')
  92. INSERT INTO @TestyWesty VALUES('11/01/2009 09:00:01')
  93.  
  94. SELECT 
  95.       CASE
  96.         WHEN dateadd(HOUR, -8, UTCDate) >= dbo.udf_GetDaylightSavingsTime(YEAR(UTCDate), 'Begin') AND dateadd(HOUR, -7, UTCDate) < dbo.udf_GetDaylightSavingsTime(YEAR(UTCDate), 'End')
  97.             THEN dateadd(HOUR, -7, UTCDate)
  98.         ELSE
  99.             dateadd(HOUR, -8, UTCDate)
  100.        END AS UTCDate
  101. FROM   @TestyWesty

URL: http://www.sqlservercentral.com/scripts/function/70984/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.