Solved

How to account for Daylight Savings Time with SQL Server 2005

Posted on 2011-03-04
2
960 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
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now