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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select * from 6 43
Need help creating a stored procedure 4 63
Delete from table 6 47
Clone table from one server.database to another server.database 24 34
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

791 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