none
Convert a given datetime to UTC

    Question

  • I am going to create a udf to convert a certain datetime to utc in sql server 2008. 

    i.e.  convert  '2001-08-09 13:29:07.753'  to UTC  (timezone -5:00 )

    anyone has any idea?


    Kianoush Torkzadeh
    Tuesday, August 09, 2011 1:31 PM

Answers

  • This should do the trick... This will work in any time zone.

    SELECT DATEADD(hh, DateDiff(hh, GetDate(), GetUTCDate()), YourDateColumn) AS ConvertedToUTC
    



    Jason Long
    Tuesday, August 09, 2011 2:31 PM

All replies

  • I think the function GETUTCDATE() might be of some help for you...

    and for additional reading you can go through this blog post

    http://blogs.msdn.com/b/bartd/archive/2009/03/31/the-death-of-datetime.aspx


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, August 09, 2011 1:36 PM
  • This should do the trick... This will work in any time zone.

    SELECT DATEADD(hh, DateDiff(hh, GetDate(), GetUTCDate()), YourDateColumn) AS ConvertedToUTC
    



    Jason Long
    Tuesday, August 09, 2011 2:31 PM
  • GETUTCDATE  is not deterministic and does not accept parameter. it is only working on the local machine time.

    I know .NET calculates the utc based on the given offset. I am looking for something similar in SQL server.  


    Kianoush Torkzadeh
    Tuesday, August 09, 2011 3:02 PM
  • Thanks Jason,

    I think I was not very clear in the requirements.

    the given datetime can be in any time zone and I would like to consider the daylight saving for the result.

     

     

     


    Kianoush Torkzadeh
    Tuesday, August 09, 2011 3:04 PM
  • If you want to control the offset manually then you simply need to use the DateAdd function.

    SELECT DateAdd(hh, 5, YourDateColumn) AS DateOffSet
    FROM TableName
    


    If you need to factor in daylight saving time, your best bet would be to build a location/calendar table that tracks which dates are included in daylight savings and which are not. 

    SQL Server doesn't track daylight saving internally so you'll need to provide this yourself in the form of a table. Just bear in mind that some locations observe DST and other do not and those that do, set the change time at different times on the change dates.


    Jason Long
    Tuesday, August 09, 2011 3:26 PM
  • Have you looked at DATETIMEOFFSET datatype?

     


    N 56°04'39.26"
    E 12°55'05.63"
    Tuesday, August 09, 2011 3:26 PM
  • Yes. I came up to the same conclusion. There is no way to calculate it in a udf. I prefer to create a SSIS package and add a script to use .NET functions to convert it.

     

    Thanks by the way.

     


    Kianoush Torkzadeh
    Tuesday, August 09, 2011 6:11 PM