Daylight Savings timezone conversion?

Question

Daylight Savings timezone conversion?

Khades

SSCrazy

Points: 2067
More actions
October 25, 2012 at 1:39 pm

#284043

Hi,
I need to convert a datetime field to a timezone before sending the information from a database to an XML file.
However, I need to account for daylight savings based on what the date that I am sending is. If the date is during a time that daylight savings was into account, then I need to adjust it differently than if the date wouldn't be in daylight savings.
For example, I would need to add 8 hours to a '2011-01-15 13:00:00.000' to account for Zulu time but only 7 hours to '2011-09-15 13:00:00.000'.
How do I do this with SQL Server?
Thanks,
Sam

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I've always handled this with a Calendar table, where the Calendar table has DaylightSavings column.

that is especially important if you fiddle with historical data, because the date ranges for DST changed changed in 2007.

in 2006 and previous years, DST was from the first sunday in April thru the last sunday in October.

For 2007 and After, DST is from the second Sunday in March to the first Sunday in November.

I've thrown this pretty comprehensive TallyCalendar Table out there, and gotten quite a bit of positive feedback on it.

TallyCalendar_Complete_With_DST.txt

To use it in your case, you would join your data to the TallyCalendar Table to your data so you can use DATEADD(hh,IsDaylightSavings ,YourDateColumn) to add/subtract the IsDaylightSavings bit to account for DST.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Khades SSCrazy Points: 2067 More actions · Answer 2

Page cannot be displayed :(.

I have a question, so if daylight savings were to change again in the future, I would have to go update this table correct?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Khades (10/25/2012)
Page cannot be displayed :(.
I have a question, so if daylight savings were to change again in the future, I would have to go update this table correct?

Yes. You'd have to. But you'd also be able to. Many implementations of DST assume current rules are valid for all days for all years. Calendar table makes it easy to control that kind of thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 4

Khades (10/25/2012)
Page cannot be displayed :(.
I have a question, so if daylight savings were to change again in the future, I would have to go update this table correct?

fixed. i left off the "h" in http when i pasted the, sorry.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!