Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005

by

Create two tables and then join to them to convert stored GMT dates to local time:

TimeZones     e.g.
---------     ----
TimeZoneId    19
Name          Eastern (GMT -5)
Offset        -5

Create the daylight savings table and populate it with as much information as you can (local laws change all the time so there’s no way to predict what the data will look like years in the future)

DaylightSavings
---------------
TimeZoneId    19
BeginDst      3/9/2008 2:00 AM
EndDst        11/2/2008 2:00 AM

Join them like this:

inner join  TimeZones       tz on x.TimeZoneId=tz.TimeZoneId
left join   DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone 
    and x.TheDateToConvert between ds.BeginDst and ds.EndDst

Convert dates like this:

dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, TheDateToConvert)

More Related Contents:

  1. Where clause not working in sql server
  2. Alias name is same as column name [closed]
  3. T-SQL stored procedure that accepts multiple Id values
  4. LocalDB deployment on client PC
  5. Understanding PIVOT function in T-SQL
  6. The order of a SQL Select statement without Order By clause
  7. How do I escape a percentage sign in T-SQL?
  8. Using RegEx in SQL Server
  9. What is the best way to auto-generate INSERT statements for a SQL Server table?
  10. SQL Server named instance with Visual Studio 2017 Installer project
  11. Get list of databases from SQL Server
  12. CAST and IsNumeric
  13. CTE to get all children (descendants) of a parent
  14. SQL Server – inner join when updating [duplicate]
  15. How do I use the CONCAT function in SQL Server 2008 R2?
  16. How do I exclude Weekend days in a SQL Server query?
  17. EXEC sp_executesql with multiple parameters
  18. SQL Server – Dynamic PIVOT Table – SQL Injection
  19. Best data store for billions of rows
  20. newid() inside sql server function
  21. How can I use a single mssql connection pool across several routes in an Express 4 web application?
  22. When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?
  23. How to add custom attributes to SQL connection string?
  24. Retrieving date in sql server, CURRENT_TIMESTAMP vs GetDate()
  25. SSIS reading LF as terminator when its set as CRLF
  26. What are some ways of accessing Microsoft SQL Server from Linux?
  27. LPAD in SQL Server 2008
  28. Why is casting from float to varchar being rounded in SQL Server?
  29. Update SQL with consecutive numbering
  30. Nested transactions in Sql Server

Leave a Comment