Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Time Zone Conversions Expand / Collapse
Author
Message
Posted Tuesday, July 10, 2012 10:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
Looking for an article that talks about and shows how to easily convert one time to another given a time zone. It would be nice if the standard EST/EDT/CST/CDT/PST/GMT, etc codes could be used to convert times easily.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1327759
Posted Tuesday, July 10, 2012 11:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Should the code be sensitive to Daylight Time vs Standard Time (US), or should it rely on "EDT" vs "EST" in the input?

If sensitive without the input being modified, should it take into account prior definitions of the begin/end dates for those transitions, or just work off of current rules?

Will it take a location into account (like Arizona, which doesn't do Daylight Time; same for Hawaii)?

Or just 3 inputs: Time, ZoneFrom, ZoneTo?

How about backwards compatibily with SQL Server 2005 and before, which don't have the Time datatype, and would have to provide DateTime?

Or, since it's an article, should it go into all of these options, and provide sample code for each? The more complex options (geo-sensitive, sensitive to changing rules by year of input value, etc.) might be pretty complex.


- 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
Post #1327776
Posted Tuesday, July 10, 2012 11:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
GSquared (7/10/2012)
Should the code be sensitive to Daylight Time vs Standard Time (US), or should it rely on "EDT" vs "EST" in the input?

If sensitive without the input being modified, should it take into account prior definitions of the begin/end dates for those transitions, or just work off of current rules?

Will it take a location into account (like Arizona, which doesn't do Daylight Time; same for Hawaii)?

Or just 3 inputs: Time, ZoneFrom, ZoneTo?

How about backwards compatibily with SQL Server 2005 and before, which don't have the Time datatype, and would have to provide DateTime?

Or, since it's an article, should it go into all of these options, and provide sample code for each? The more complex options (geo-sensitive, sensitive to changing rules by year of input value, etc.) might be pretty complex.


I think this will be a bit complex. Yes, I'd like to take into account the EST v EDT. I don't know that I'd go back in time to the old DST time changes (Apr/Oct) but work with new ones.

No backwards compatability with pre 2008.

Sample code, and a function to use. If there's something that can't be covered or seems out of scope, say that.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1327785
Posted Tuesday, July 10, 2012 11:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Happen to know where to get a list of all the time-zone abbreviations? I tried a quick search online, including a couple of wikipedia articles, and couldn't get anything other than the statement that "Local abbreviations like EST and PDT are not part of any international standard and shouldn't be used".

The US ones are easy. Could also provide a framework that allows entering localized versions and avoid that whole thing. Would that do for what you want the article to cover?


- 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
Post #1327792
Posted Tuesday, July 10, 2012 1:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
This work? http://www.timeanddate.com/library/abbreviations/timezones/

A framework would be nice.

Looking to fill a hole, or a partial hole, in dealing with time zones in T-SQL.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1327846
Posted Monday, July 16, 2012 8:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Article and script (for the data) sent (by e-mail).

- 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
Post #1330148
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse