Convert UTC Time to Pacific Time Query:crazy:

  • 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

  • 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

  • CLR UDF will help you

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

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

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

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

  • I figured it out.

    Thank you.

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

  • drodriguez,

    You say you figured it out. Please share your solution so others can learn from it. Thanks!!

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

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

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

  • Do you have a better solutions?

    -Dave

  • 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 16 total)

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