drodriguez-762178
SSCommitted
Points: 1795
More actions
April 17, 2009 at 3:45 pm
#133186
Anyone know of a function I can use to convert the UTC date/time field to Pacific Time? I know I can use dateadd to subtract the 8 hours, but I would like to know if there is a better way.
UTC Time
2009-04-17 21:44:10.717
Current Time
2009-04-17 14:44:10.717
Thanks,
Dave
arun.sas
SSChampion
Points: 11831
More actions
April 17, 2009 at 9:31 pm
#980116
Hi,
One of the way
Declare @abc TABLE
(
SLNO int,
UTC Datetime,
CON_DATE as (dateadd(hh,-8,UTC))
)
insert into @abc(slno,UTC) values(1,Getdate())
select * from @abc
ARUN SAS
fayilt
Right there with Babe
Points: 727
More actions
April 18, 2009 at 5:35 am
#980162
CLR UDF will help you
john.arnott
SSChampion
Points: 11882
April 18, 2009 at 10:32 pm
#980312
Knowing your time zone (Pacific) is a good start. But of course you also will probably want to account for whether daylight saving time is in effect, making the difference -7 hours rather than -8. If your server is set to the Pacific time you want (standard or daylight), you may use the DateDiff between getDate() and getUTCDate() as your offset, using DateAdd to apply it to your UTC time.
Declare @MyDateUTC datetime
Set @MyDateUTC = '2009-04-19 04:12'
select @MydateUTC as MyDateUTC
,DateAdd(hh,DATEDIFF(hh, GetUtcDate(), GetDate()), @MyDateUTC) as MyDatePacific
MyDateUTC MyDatePacific
----------------------- -----------------------
2009-04-19 04:12:00.000 2009-04-18 21:12:00.000
(1 row(s) affected)
Of course, the current difference (-7) may not be valid if you're looking at a date from February, for instance. For an application that looks at historic times and needs to convert any from UTC to Pacific Time, I'd think you'll need a table of the start and end dates for Daylight Saving Time to know whether it's -7 hours or -8 hours. A web search should turn up the basic data for you. Depending on how far back you go, the dates and rules for determining the dates Daylight Saving Time started and ended have changed many times over the years.
Goldie Lesser
SSCertifiable
Points: 6155
More actions
April 20, 2009 at 5:57 pm
#981126
drodriguez (4/17/2009)
Anyone know of a function I can use to convert the UTC date/time field to Pacific Time? I know I can use dateadd to subtract the 8 hours, but I would like to know if there is a better way.UTC Time
2009-04-17 21:44:10.717
Current Time
2009-04-17 14:44:10.717
Thanks,
Dave
If you do not need exact times in your data, and if you are only ever converting from Pacific to UTC and not other time zones, then you can use the method John suggested.
For better accuracy, you can create a table in your database which stores the time zones and when each country switches to daylight savings. You can download the applicable functions and data from The Code Project http://www.codeproject.com/KB/database/ConvertUTCToLocal.aspx
Unfortunately, even this will not be accuarate for historic data, as countries change the daylight savings dates each year.
In SQL Server 2008, there is a new datatype called DateTimeOffset which stores the offset together with the date, so that dates can be converted accurately.
kylemwhite
SSC Enthusiast
Points: 190
More actions
April 20, 2009 at 11:19 pm
#981203
If you are only concerned with dates after 2006, the Microsoft functions will probably be accurate. If, however, you have historical data, then do not trust the Microsoft functions. It gets tricky. I have written about this subject on my blog: Kyle's Technobabble.
Goldie Lesser
SSCertifiable
Points: 6155
More actions
April 21, 2009 at 7:45 am
#981428
kylemwhite (4/20/2009)
If you are only concerned with dates after 2006, the Microsoft functions will probably be accurate. If, however, you have historical data, then do not trust the Microsoft functions. It gets tricky. I have written about this subject on my blog: Kyle's Technobabble.
Which Microsoft functions are you talking about?
drodriguez-762178
SSCommitted
Points: 1795
More actions
April 21, 2009 at 8:55 am
#981502
I figured it out.
Thank you.
kylemwhite
SSC Enthusiast
Points: 190
More actions
April 21, 2009 at 9:48 am
#981565
Old Hand: I guess my post was a bit incomplete. I am referring to the TimeZone.ToUniversalTime() function in .Net. I've updated my blog post as well and also included the Java functions used (DateFormat.setTimeZone() and TimeZone.getTimeZone())
john.arnott
SSChampion
Points: 11882
More actions
April 21, 2009 at 9:50 am
#981570
drodriguez,
You say you figured it out. Please share your solution so others can learn from it. Thanks!!
drodriguez-762178
SSCommitted
Points: 1795
More actions
April 21, 2009 at 9:53 am
#981574
I subtracted 8 hours using a dateadd(hh,-8, column).
john.arnott
SSChampion
Points: 11882
More actions
April 21, 2009 at 10:01 am
#981582
drodriguez (4/21/2009)
I subtracted 8 hours using a dateadd(hh,-8, column).
So this was just for data with dates before March 8 this year, after which it would be 7 hours.... OK.
fayilt
Right there with Babe
Points: 727
More actions
April 23, 2009 at 12:12 pm
#983174
I hope, that this is the temporary solution, otherwise you have to change your code 2 times a year. Moreover, you can not get valid date in the past.
drodriguez-762178
SSCommitted
Points: 1795
More actions
April 23, 2009 at 12:25 pm
#983184
Do you have a better solutions?
-Dave
john.arnott
SSChampion
Points: 11882
More actions
April 23, 2009 at 12:50 pm
#983201
drodriguez (4/23/2009)
Do you have a better solutions?-Dave
Well..... Yes.
Take another look at my suggestion above. Look at Goldie's first post. Consider reading the link Kyle gave to his blog for information on the MS conversion function. This last may actually be your best bet, even if (or maybe because) it would be in the presentation of your data and not the SQL.
