Convert PST format to UTC format in SQL Server 2005.

• wong.bing

  SSC Veteran

  Points: 231

  More actions

  May 26, 2009 at 2:27 pm

  #134589

  I have a need to convert PST format to UTC format in SQL Server 2005. Example is:

  Convert from UTC to Local date:

  convert(char(10),dateAdd(hour, datediff(hour, getutcdate(), getdate()),dateadd(second, call_time, '1/1/1970')),101) = '05/26/2009'

  Call_time is an integer field which contains UTC data.

  Now, I have a need to stored UTC data in an integer field. How can I convert local time to UTC as integer?

  Thanks very much!

  Bing

• Paul White

  SSC Guru

  Points: 150468

  More actions

  May 27, 2009 at 1:51 am

  #1000385

  wong.bing (5/26/2009)

  ---

  I have a need to convert PST format to UTC format in SQL Server 2005. Example is:

  Convert from UTC to Local date:

  convert(char(10),dateAdd(hour, datediff(hour, getutcdate(), getdate()),dateadd(second, call_time, '1/1/1970')),101) = '05/26/2009'

  Call_time is an integer field which contains UTC data.

  Now, I have a need to stored UTC data in an integer field. How can I convert local time to UTC as integer?

  Unless you store the timezone offset with the data at the time of writing, dealing with timezone issues in 2K5 can be problematic, because of daylight savings for example.

  One solution is to use a CLR routine to convert to and from timezones using the .NET framework. This has issues too, however, as the required libraries at not part of the default 'safe' set - as far as I recall anyway.

  Another solution is to use one of the online databases to provide timezone information. See http://en.wikipedia.org/wiki/Zoneinfo for a starting point on this.

  SQL Server 2K8 brings some relief for this issue with the new DATETIMEOFFSET and related facilities. It is by no means comprehensive however.

  As far as storing a datetime in an int is concerned, there are a number of ways to do this, some undocumented, some unwise, and still others that are merely questionable. If you don't need a time component (unlikely given your comments thus far), a common solution is to format the date as YYYYMMDD and convert that directly to an int.

  Paul

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi