February 25, 2011 at 10:11 am
My SQL server is in Pacific Time. My branches are all over the country. I can determine based on location what respective time zones each location is at. Is there a CONVERT or CAST or something I can use to "change" the time stamps of activities to the respective time zone?
(FYI - I don't want to change the data in the database, just the data in the extract (aka SELECT statement))
😎
February 25, 2011 at 10:39 am
Looking at this differently, how about using the GetUTCDate() function to return a common date/time?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 25, 2011 at 10:50 am
SQL_Student (2/25/2011)
My SQL server is in Pacific Time. My branches are all over the country. I can determine based on location what respective time zones each location is at. Is there a CONVERT or CAST or something I can use to "change" the time stamps of activities to the respective time zone?(FYI - I don't want to change the data in the database, just the data in the extract (aka SELECT statement))
😎
While Wayne's fix would be the long term preference, in the short term you'll probably need to do something like this:
print dateadd( dd, case when 1 =1 then 1 else 0 end, getdate())
Modify your case statement to deal with your timezone recognition.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 25, 2011 at 11:48 am
You're right to not want mess with the data as stored on the database. Whether it's recorded as Pacific or Greenwich (UTC) time, I think it would be much less confusing to have all times recorded consistently regardless of which time zone where the associated branch is. And if you're stuck with Pacific time, so be it, although if you were building a new application, UTC would be preferable. The question then is how to adjust the retrieved time show it in local time for the branch.
The basic function to use would be DateAdd(). Use that to add the appropriate amount to the times in your database. The real tricky part will be obtaining the appropriate offset for each location. Although the offset may be in hours, I'd recommend using minutes for ease of conversion to SQL 2008 data types that integrate time zone offsets and also for possible international use -- some time zones like India's are not even hours apart from UTC. The final wrinkle will be to be sure you can handle the oddities of daylight saving time. Even if your company isn't there now, consider the possibility of their opening a branch in Arizona, which does not observe DST. That means that although in the winter, their clocks on MST are one hour ahead of Pacific, in the spring when we on the West Coast move to PDT, they stay on MST and there's no offset. Although your company may not have any middle-of-the night activity, you may also want to consider how to handle East Coast transactions recorded with Pacific Daylight times when the East coast is already on Standard time, or recorded as PST when the East Coast has moved to EDT.
Oh. Right. You asked for code.... Let's assuime that you've worked out all the wrinkles described above. The basic conversion would look something like this:
Select DateAdd(mi, + TimeZoneLookup.TimeZoneOffset, Tran.TransactionTime) as TranTimeConverted
,Tran.OtherData
from Tran
join TimeZoneLookup on TimeZoneLookup.BranchID = Tran.BranchID
I hope this helps get you started. It would be interesting to me and probably helpful to many to see how your final solution works.
February 25, 2011 at 2:57 pm
Thank you very much to everyone who posted. All entries were helpful and I was able to use John's post to solve my problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply