How to account for Daylight Savings Time with SQL Server 2005

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


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

Open in new window

Question by:jbaird123
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

Accepted Solution

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.

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
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


Open in new window


Author Closing Comment

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!

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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 …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

763 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