Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to account for Daylight Savings Time with SQL Server 2005

Posted on 2011-03-04
2
Medium Priority
?
974 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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

963 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