Post reply

Convert UTC Time to Pacific Time Query:crazy:

  • 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

  • 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

  • April 18, 2009 at 5:35 am

    #980162

    CLR UDF will help you

  • 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.

  • 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.

  • 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.

  • 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?

  • April 21, 2009 at 8:55 am

    #981502

    I figured it out.

    Thank you.

  • 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())

  • 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!!

  • April 21, 2009 at 9:53 am

    #981574

    I subtracted 8 hours using a dateadd(hh,-8, column).

  • 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.

  • 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.

  • April 23, 2009 at 12:25 pm

    #983184

    Do you have a better solutions?

    -Dave

  • 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.

Viewing 15 posts - 1 through 15 (of 15 total)

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