Solved

How to account for Daylight Savings Time with SQL Server 2005

Posted on 2011-03-04
2
963 Views
Last Modified: 2012-05-11
I have a function that will convert dates/times from UTC to the local time zone, but this function does not take into account daylight savings of past or future dates (see function below).

The problem is that this code uses the "getdate()" function - which returns the current date and time and this is used in determining the offset.  This is great for converting UTC to Local time as long as the the time you are converting is also in Daylight Savings Time at the same time you are.

For example:

Today is March 3rd, and we are not in DST (I am on Eastern time).  Today, if I run the code below for the date of 3/1/2009 8:00AM UTC  I will get 3/1/2009 3:00AM EST - which is correct.  But if I run this same program again on July 1, 2011 (EDT), I will get 3/1/2009 4:00AM EST  which is wrong.

I have seen other solutions posted on Experts Exchange to work around this, but they all seem to involve a lot of hard coding of dates and they don't look like very clean solutions.

What is the best way to solve this problem?

Thanks.


create function dtUtc2Local(@dtUtc datetime)returns datetime as
--Parameter: UTC datetime 
--returns: Corresponding local time
begin
  DECLARE @TimeDiff int
  Declare @dtLocal as datetime
  --get time difference
  SET @TimeDiff = DATEDIFF(hh, GETUTCDATE(), GETDATE())
  SELECT @dtLocal = DATEADD(HH, @TimeDiff, @dtUtc)
  return @dtLocal
END

Open in new window

0
Comment
Question by:jbaird123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Accepted Solution

by:
bhoenig earned 500 total points
ID: 35037843
This is a function that will work for day light savings in 2007 to future years.  You would have to modify the function to add the correct calculations for prior years.  

http://en.wikipedia.org/wiki/Energy_Policy_Act_of_2005

These are my tests of the function:

select dbo.fnIsDayLightSavings(getdate())
select dbo.fnIsDayLightSavings('3/12/2011')
select dbo.fnIsDayLightSavings('3/13/2011')
select dbo.fnIsDayLightSavings('3/14/2011')
select dbo.fnIsDayLightSavings('11/6/2011')
select dbo.fnIsDayLightSavings('11/7/2011')

create function fnIsDayLightSavings (@GivenDate datetime)
returns int
as 
begin
set @GivenDate = cast(floor(cast(@GivenDate as float)) as datetime)

declare @calcStartDate datetime
declare @calcAddDays int
declare @calcWeekOfMonth int
declare @calcDayOfWeek int
declare @BeginDST datetime
declare @EndDST datetime

/* 2nd Sunday of March */
set @calcStartDate = cast(cast(year(@GivenDate) as varchar) + '-03-01' as datetime) -- Always set to the first of the month
set @calcWeekOfMonth = 2 -- 2nd 
set @calcDayOfWeek = 1 -- Sunday
if datepart(dw, @calcStartDate) <= @calcDayOfWeek 
	set @calcWeekOfMonth = @calcWeekOfMonth - 1
set @calcAddDays = (@calcWeekOfMonth * 7) + @calcDayOfWeek - datepart(weekday, @calcStartDate)
set @BeginDST = dateadd(dd, @calcAddDays, @calcStartDate)
	
/* 1st Sunday in November */
set @calcStartDate = cast(cast(year(@GivenDate) as varchar) + '-11-01' as datetime) -- Always set to the first of the month
set @calcWeekOfMonth = 1 -- 1st 
set @calcDayOfWeek = 1 -- Sunday
if datepart(dw, @calcStartDate) <= @calcDayOfWeek 
	set @calcWeekOfMonth = @calcWeekOfMonth - 1
set @calcAddDays = (@calcWeekOfMonth * 7) + @calcDayOfWeek - datepart(weekday, @calcStartDate)
set @EndDST = dateadd(dd, @calcAddDays, @calcStartDate)


if @GivenDate between @BeginDST and @EndDST 
	return 1

return 0

end

Open in new window

0
 

Author Closing Comment

by:jbaird123
ID: 35038019
This is perfect and exactly what I needed. Thank you.

One small suggestion (for the perfectionists out there):  It could be modified to take into account the specific time of day to determine whether it is DST or not.  So on the actual days when the change occurs, part of the day is actually DST and the other part is not.  In any case, this is a very minor detail and not needed for my purposes.

Thanks again!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question