December 20, 2009 at 2:39 pm
We have a project that stores several date/time fields into a database. Our project team has to extract this data and put it into our warehouse. The problem comes that some of the date/times are in GMT time and need to be held as such but we also need the local time to help link to other tables.
Now the best thing would be for the source project to store both GMT and local times at the time the data is stored in the database. However for a variety of reasons (including office politics) this 'just ain't gonna happen'. :crazy:
It is up to us to produce a solution to for this. 🙁
A simple solution is to do a datediff of gmt date and getdate() to give a time difference. This could be used to convert the gmt date and the now converted local time can be stored. However we will need to do this over a range of dates, i.e. not just todays, so the solution needs to take account the fact that the date range may contain some dates that are in daylight savings time and some not.
Does anyone have any thoughts on this?
December 21, 2009 at 7:43 am
Assuming you are using SQL Server 2008 since this is 2008 forum, have you looked at the DateTimeOffset data type? You could use it or you could store the offset in a tinyint column and use that. So the DST would be taken into account as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 21, 2009 at 12:30 pm
Unfortunately it is SQL Server 2000.
Just realised i got the right sub post. i.e. newbies, but the wrong thread 2008. :blush:
John
December 21, 2009 at 12:49 pm
Happens all the time, no worries.
YOu could use the second suggestion, have a GMT offset column that is tinyint. So if your offset is 5 (EST) you'd do DateAdd(hour, -offset, time) for GMT at that time.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 21, 2009 at 12:56 pm
Jack Corbett (12/21/2009)
Happens all the time, no worries.YOu could use the second suggestion, have a GMT offset column that is tinyint. So if your offset is 5 (EST) you'd do DateAdd(hour, -offset, time) for GMT at that time.
Wouldn't a decimal (4,1) work better? If I recall thare are some time zones that are off by 30 minutes, not just full hours. Plus you need to + or - for the offset.
December 21, 2009 at 1:08 pm
Ah, I always forget about those odd ball time zones. Just like the one in Indiana that doesn't do DST. Good catch!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 21, 2009 at 1:11 pm
Jack Corbett (12/21/2009)
Ah, I always forget about those odd ball time zones. Just like the one in Indiana that doesn't do DST. Good catch!
Well, at previous employer we supported remote offices around the world. Had to be cognizant of the time zone differences.
December 21, 2009 at 6:17 pm
Jack Corbett (12/21/2009)
Ah, I always forget about those odd ball time zones. Just like the one in Indiana that doesn't do DST. Good catch!
Back here on planet earth Indiana politicians took about one billion here and there bribe from uncle Sam to join the daylight time change in 2006 but Arizona is still no time change.
😉
Kind regards,
Gift Peddie
December 22, 2009 at 12:08 pm
I think Arizona has it right (no DST)
😛
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2009 at 9:29 am
If you are working with historical data, you're in trouble as the dates of the switch in and out of DST changed from year to year. As well as the fact that certain countries, etc. vote on a yearly basis whether or not to implement DST. Just look at the continual Microsoft Windows Updates (KBs) that "fix" the DST computation.
You would need a separate table containing the shift in/shift out dates of DST possibly by geographic location (e.g. by country all the way down to local area (e.g. state)). AND you would have to know from what location the "event" occurred. Ugh!
If this is a "new" application, I'd seriously look at a.) using the new SQL Server 2008 date datatypes (if you are using SQL Server 2008) or b.) storing the TZ offset as a separate column. But if you're going to add another column for the offset, why not just store UTC?
December 23, 2009 at 9:35 am
Mauve (12/23/2009)
If you are working with historical data, you're in trouble as the dates of the switch in and out of DST changed from year to year. As well as the fact that certain countries, etc. vote on a yearly basis whether or not to implement DST. Just look at the continual Microsoft Windows Updates (KBs) that "fix" the DST computation.You would need a separate table containing the shift in/shift out dates of DST possibly by geographic location (e.g. by country all the way down to local area (e.g. state)). AND you would have to know from what location the "event" occurred. Ugh!
If this is a "new" application, I'd seriously look at a.) using the new SQL Server 2008 date datatypes (if you are using SQL Server 2008) or b.) storing the TZ offset as a separate column. But if you're going to add another column for the offset, why not just store UTC?
.NET 3.5 and SQL Server 2008 comes with native timezone defined and there is existing .NET code on Codeproject that can create timezone. When not defined it can get complex.
Kind regards,
Gift Peddie
December 24, 2009 at 3:26 am
IMHO the only version of SQL Server that handles international and DST-affected times correctly is SQL Server 2008 when using the UTC-aware data types. If you are not using these then things are a mess.
The best you can do outside of SQL Server 2008 is to make up your own UTC aware date/time handling. If you do not do this then trying to keep track of international and historical events is probably never going to be perfect. Most atempts fall down as soon as you want to reconcile DST to UTC, as often you do not know the locale that the data applies to in order to find the DST changeover for the year in question.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply