May 9, 2012 at 10:57 am
Hello,
I am trying to convert time between zones.
i.e., when a transaction takes place in China...I need to get US EST time to post that transaction.
How do i do that with out using DATEADD function?
Thank you....
May 9, 2012 at 11:02 am
can you change the processes to start using GETUTCDATE instead of GETDATE when you are building your time stamps?
that's greenwhich(sp?) time, so you can better compare dates between data at different locations.
select getutcdate() As UTCDATE,getdate() As TheDate
/*
UTCDATE TheDate
2012-05-09 17:00:58.180 2012-05-09 13:00:58.173
*/
Lowell
May 9, 2012 at 12:53 pm
Lowell, Thanks for the reply.
When I try to offset UTC time to get US time, Is there any way that I can get daylight saving differences also?
May 9, 2012 at 1:06 pm
sure, the datediff between getdate and getutcdate will giveyou the current offset; that willl be 4 or 5 depedning on whether daylight savings is in effect or not:
some timezones are not on the hour, but by half hour or even off by 15 minutes; so it's proably better to get the difference in minutes, unless you are only dealing with zones you know are exact-hour differences:
--returns 4
SELECT datediff(hour,getdate(),getutcdate())
--returns
SELECT datediff(minute,getdate(),getutcdate()) 240
if i need to know if a date was daylight savings or not, i use a calendar table.
the rules for the US changes; so far example dates prior to 2006 and before, Daylight savings ran from the first sunday in April thru the last sunday in October.
Starting in for 2007 and After.
it starts from the second Sunday in March to the first Sunday in November
.
that makes it a bear to try to do inside a query, but it's trivial if you join agaisnt a calendar table
here's an example of mine:
TallyCalendar_Complete_With_DST.txt
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply