1

My server save time in LOCAL time not including Time Zones info in dateTime2. Server is in -7 GMT. I need to create an SQL which show records but converted at TimeZone for User (+1 GMT).

Let's imagine I have this record '2014-05-27 01:00:00' Should be displayed as '2014-05-27 09:00:00'

Using the following script I am not able to get the result desired. Could you please point me out the problem?

SELECT CONVERT(datetime, 
    SWITCHOFFSET(CONVERT(datetimeoffset, 
    '2014-05-27 01:00:00'), 
    DATENAME(TzOffset, SYSDATETIMEOFFSET())))
GibboK
  • 71,848
  • 143
  • 435
  • 658
  • http://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement 10s of googling – Janne Matikainen May 27 '14 at 09:15
  • Thanks for your comment but it does not work as intended as I should be able to set the time zone in the query... as SYSDATETIMEOFFSET() it is the time for the server not for the User querying the db. – GibboK May 27 '14 at 09:18
  • Please, post the output of the script after execution to give you more reasonable answer. By the way, your code works fine for me: if admit the date '2014-05-27 01:00:00' as a UTC date, the script returns '2014-05-27 05:00:00' (+4 GMT) which is correct for my location. – xacinay May 27 '14 at 09:25
  • Is the datetime in your database in your servers local time and not in UTC, ie it should be 08:00 UTC but instead its saved in GMT-7 (01:00)? And you want it to display GTM+1 09:00? – Janne Matikainen May 27 '14 at 09:27
  • time in the db is saved at LOCAL time for the server, I need to show it as GMT +1. Sorry for the confusion. – GibboK May 27 '14 at 09:55
  • How about DST? Will you update the code twice a year? Saving LOCAL is always a bad idea, always stick to UTC in the storage, render local in presentation. – Remus Rusanu May 27 '14 at 19:33

1 Answers1

0

You can replace DATENAME() with a hard-coded '+01:00' value. Keep in mind that using the TZOffset functionality in SQL Server is not yet DST aware, so there will be discrepancies near DST switch over events in March and October.

Jaaz Cole
  • 3,119
  • 1
  • 15
  • 20