Reading in UTC format and generate the appropriate local time that is stored for that record

Question

Reading in UTC format and generate the appropriate local time that is stored for that record

vasiles1

SSC Rookie

Points: 31
More actions
April 27, 2010 at 6:54 am

#156909

I have below an example of data result from a select statement. What has become a challenge is to include in this query, the ability to convert the UTC value to a local time value based on the timezone offset data in the additional returned attributes. I ideally this could be done on the fly and a new attributed be populated with the converted data.
EventTimeUTC = 2010-04-21 12:07:27.000
Bias = -60
StandardBias = 0
Daylightbias = -60
Standardday = 0
Standardweek = 5
standardmonth = 10
standardhour = 3
standardminute = 0
standardsecond = 0
daylightday = 0
daylightweek = 5
daylightmonth = 3
daylighthour = 2
daylightminute = 0
daylightsecond = 0
gmtoffset = 60
daylightsave = 1

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can do it with the dateadd and datediff function. With datediff function you can calculate the difference in minutes between the local time and UTC time. With dateadd function you can add it to the value that you get. Here is an example:

declare @MyDate datetime

set @MyDate = '2010-04-27 15:59:15.137' --UTC time

--Getting the time according to the local datetime

select DATEADD(mi,datediff(mi, getutcdate(),getdate()),@MyDate)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vasiles1 SSC Rookie Points: 31 More actions · Answer 2

Thanks for the reply. I can see the new time calculated. What I don't understand is how I can input the offset informatin to determine the new time. The offset values will vary according to timezone and are not relative to the local system time.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Notice that I’m using to functions. The first function is GETUTCDATE() that shows the UTC time. The second function is GETDATE() that shows the local time. I check the difference in minutes between those 2 functions and I have the time difference between the local time and UTC time. This way you can run it at any time zone, because I don’t hardcode the time difference.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/