none
Converting Dates stored in UTC to GMT

    Question

  • Afternoon all,

    Our CRM stores datestime in UTC time so there is an hours difference between the event and the date time captured during day light savings. does any one know of a way i can convertthese date times back to GMT using SQL, a function perhaps?

    Thanks

    BB


    • Edited by BI_Baracus Tuesday, July 09, 2013 2:11 PM
    Tuesday, July 09, 2013 2:09 PM

Answers

  • Hi Bl_Baracus,
    As you say, the datetime will always be converted into UTC in the database based on the user's timezone setting. 
    There are a number of solutions to this absolute date issue:
    1. Adjust the date/time at point of entry (JavaScript or PlugIn) and convert to mid-day (12:00) so that any time conversion will not move it over the date line. This will only work if you don't have any offices that are more than 12 hours apart.
    2. Write a plugin that intercepts any Retrieve/RetrieveMultiple messages and adjust the time to correct for the time zone offset. This would only work when a date is displayed in a Form or Data Grid – it would not work with SQL based reports or when dates are compared within an advanced find search criteria.
     

    3. Store the date of birth as a string or 3 options sets for year, month and day – this is in fact the only way to completely avoid the time zone conversion issue for absolute date fields.

    For example:

    PRINT 'Date of birth test ( 12:00 corrected)'
    DECLARE @utc datetime = '2001-11-26 11:00:00'
     
    --(GMT+01:00) Brussels, Copenhagen, Madrid, Paris
    -- Entered as 2001-11-26 (sent as 2001-11-26 12:00:00)
    PRINT 'Paris (GMT+1)            ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)
    PRINT 'UTC                      ' + CONVERT(nvarchar(30),@utc,120)
    --(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 
    PRINT 'New York (GMT-05:00) ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)
    Here are some references:
    • Marked as answer by BI_Baracus Tuesday, July 09, 2013 3:22 PM
    Tuesday, July 09, 2013 2:33 PM

All replies

  • Hi Bl_Baracus,
    As you say, the datetime will always be converted into UTC in the database based on the user's timezone setting. 
    There are a number of solutions to this absolute date issue:
    1. Adjust the date/time at point of entry (JavaScript or PlugIn) and convert to mid-day (12:00) so that any time conversion will not move it over the date line. This will only work if you don't have any offices that are more than 12 hours apart.
    2. Write a plugin that intercepts any Retrieve/RetrieveMultiple messages and adjust the time to correct for the time zone offset. This would only work when a date is displayed in a Form or Data Grid – it would not work with SQL based reports or when dates are compared within an advanced find search criteria.
     

    3. Store the date of birth as a string or 3 options sets for year, month and day – this is in fact the only way to completely avoid the time zone conversion issue for absolute date fields.

    For example:

    PRINT 'Date of birth test ( 12:00 corrected)'
    DECLARE @utc datetime = '2001-11-26 11:00:00'
     
    --(GMT+01:00) Brussels, Copenhagen, Madrid, Paris
    -- Entered as 2001-11-26 (sent as 2001-11-26 12:00:00)
    PRINT 'Paris (GMT+1)            ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)
    PRINT 'UTC                      ' + CONVERT(nvarchar(30),@utc,120)
    --(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 
    PRINT 'New York (GMT-05:00) ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)
    Here are some references:
    • Marked as answer by BI_Baracus Tuesday, July 09, 2013 3:22 PM
    Tuesday, July 09, 2013 2:33 PM
  • Thanks for the information. Unfortunately i need the date and time to be amended. at least I know it cant be done outside of the filtered views in Dynamics. they are so slow though.

    Thanks for the info. 

    BB

    Tuesday, July 09, 2013 3:22 PM
  • Could you elaborate a little more?  For most purposes, UTC and GMT are interchangeable, and SQl server gets its local time from its computer, which almost always is adjusting for DST changes.

    In what may be a similar circumstance, I once had a call center data set that was recording calls received in Central time (automatically adjusting for CST and CDT change).  The reporting requirement was to local midnight to midnight activity for Australia and France.  I modified the calendar table used by other reports to include the UTC offset for the US Central time zone as well as the international zones using.  For any given day the report would know whether Sydney was 15, 16, or 17 hours ahead of Chicago and slice the data appropriately. 

    Good luck,

    Tim Mills-Groninger

    Tuesday, July 09, 2013 3:43 PM