none
GMT - PST Conversions

    Question

  • Hi am new to  sql server , not sure if this is the right place to post such kind of question here...i know it may be a basic one for the people here...

    I have a datetime field which is in GMT ,

    1.i need to convert it into PST
    2.Get the only Date from the converted PST time
    3.write a case statement to adjust it to day light savings and Standard(Writing Case statements is what i was thinking to write the conditions when the Datetime falls in Standard or Day light savings , please suggest if anything better than writing Case statements)

    ---------My insert Statement ---------------------------------
    Insert INTO DW_T_ASW_JOBDATA
    (
    [FINISH_DAY] ,
    [FINISH_TIME] ,
    [FINISH_TIME_GMT] )

    SELECT
    dateadd(d, (datediff(d, 0,dateadd(HOUR,-8,EC_FINISH_TIME))),0),
    dateadd(HOUR,-8,EC_FINISH_TIME),
    EC_FINISH_TIME
    FROM dbo.DW_T_EC_SBA )

    i am able to get the values in standard time, thats by subtracting 8 , but on day light savings i need to subtract by 7 hrs ,

    so i need to ,

    write CASE statements for the FINISH_TIME , FINISH_DAY fields while converting then to PST , which is in GMT , and when converted if the PST time falls in STANDARD timing then - 8 or if the PST falls in DAY LIGHT SAVING - 7

    i am new to USA , so bit confused with this DAY LIGHT SAVINGS , as in my country the time is same all year around :-)

    i am sure people might have done this before , is their any function
    which can directly be used to convert the day light saving and standard timngs ...

    i have learnt a bit about datetimeoffset() , but not sure how i use it in my above query ...

    Please Help.....

    Thanks in Advance
    Monday, February 08, 2010 3:41 AM

Answers

  • The following should work

    CASE WHEN YourGMTDateCol BETWEEN  '11-mar-2007' and '4-nov-2007'    THEN DATEADD(hh, -7, YourGMTDateCol)

         WHEN YourGMTDateCol BETWEEN  '9-mar-2008' and '2-nov-2008'       THEN DATEADD(hh, -7, YourGMTDateCol)

         WHEN YourGMTDateCol BETWEEN  '8-mar-2009' and '1-nov-2009'     THEN DATEADD(hh, -7, YourGMTDateCol)

         WHEN YourGMTDateCol BETWEEN '14-mar-2010' and '7-nov-2010'     THEN DATEADD(hh, -7, YourGMTDateCol)

    ELSE
        DATEADD(hh, -8, YourGMTDateCol)

    End 

     

     

    • Marked as answer by priyapinky Monday, February 08, 2010 8:14 PM
    Monday, February 08, 2010 7:39 PM

All replies

  • Generally you dont have to worry about the daylight saving changes in your windows operating systems . This gets updated on the correct day according to location that is set in your windows clock and you must be knowing that SQL server gets time and other system related properties from windows OS .
    Thanks, Leks
    Monday, February 08, 2010 6:07 AM
    Answerer
  • I am not sure if you got my question right , i am pulling the data from another database in which the datetime feilds are in GMT , So as am in Pacific Zone , while entering that data into my DB tables which are further used for reporting , i need convert them into PST , The other data base from which am pulling the data has previous 4 years worth of data so while pulling i just cannot -8 as it has to be -7 during the day light savings time ,
    So i am not sure how i could do this
    Somethings came up in my mind are

    1. Write Case statements like if the finish time falls between specific dates -8 or else -7(Try to google to know what are the day light saving dates for previous year and future years)
    2.Some one suggested me to use sysdatetimeoffset , but not sure how i could use in my query as its giving me my systems date obviously...

    So please help me ..

    Hope we are same page in understanding the question, let me know if its not clear i will try to re-edit my question , i have been trying this since 3 days :-(

    Thanks in Advance...

     

    Monday, February 08, 2010 5:41 PM
  • Your best bet in this case is to use power of .NET in SQL

    Timezone conversion used to be a nightmare in SQL as well as  .NET and until framework 3.5 where Microsoft came up with TimeZoneInfo class

    using SQL CLR Stored procedure and TimeZoneInfo class in .NET 3.5 

    Look for a method call 
    TimeZoneInfo ConvertTime Method (DateTime, TimeZoneInfo, TimeZoneInfo)
     http://msdn.microsoft.com/en-us/library/bb382770.aspx


    Monday, February 08, 2010 5:54 PM
  • Am actually new to sql server and .NET in SQL is something like Alein language , i tried looking into the link given above , could pleeease let me know how i could use this in my select query above ?

    Monday, February 08, 2010 6:01 PM
  • I found that there is a function in sql server datetimeoffset which is similar to ORACLE new_time() function ,

    here is the code for the oracle funtion :

    CASE

                WHEN new_time(finish_time,'gmt','pdt') between '11-mar-2007' and '4-nov-2007'

                      or new_time(finish_time,'gmt','pdt') between '9-mar-2008' and '2-nov-2008'

                      or new_time(finish_time,'gmt','pdt') between '8-mar-2009' and '1-nov-2009'

                      or new_time(finish_time,'gmt','pdt') between '14-mar-2010' and '7-nov-2010'

                THEN new_time(finish_time,'gmt','pdt')

                ELSE new_time(finish_time,'gmt','pst')

          END as QUEUE_TIME,

    How do i convert this using datetimeoffset () ??/

    Please help!

    Monday, February 08, 2010 7:14 PM
  • The following should work

    CASE WHEN YourGMTDateCol BETWEEN  '11-mar-2007' and '4-nov-2007'    THEN DATEADD(hh, -7, YourGMTDateCol)

         WHEN YourGMTDateCol BETWEEN  '9-mar-2008' and '2-nov-2008'       THEN DATEADD(hh, -7, YourGMTDateCol)

         WHEN YourGMTDateCol BETWEEN  '8-mar-2009' and '1-nov-2009'     THEN DATEADD(hh, -7, YourGMTDateCol)

         WHEN YourGMTDateCol BETWEEN '14-mar-2010' and '7-nov-2010'     THEN DATEADD(hh, -7, YourGMTDateCol)

    ELSE
        DATEADD(hh, -8, YourGMTDateCol)

    End 

     

     

    • Marked as answer by priyapinky Monday, February 08, 2010 8:14 PM
    Monday, February 08, 2010 7:39 PM