Post reply

Daylight Savings timezone conversion?

  • 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

  • October 25, 2012 at 1:52 pm

    #1552925

    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!

  • October 25, 2012 at 2:01 pm

    #1552934

    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?

  • October 25, 2012 at 2:04 pm

    #1552938

    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

  • October 25, 2012 at 2:16 pm

    #1552944

    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!

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

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