Posted By

g8rpal on 09/30/10


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

bobbym245


Get daylight savings time


 / Published in: SQL
 

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

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.

  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

Report this snippet  

You need to login to post a comment.