jbaird123
asked on
How to account for Daylight Savings Time with SQL Server 2005
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!