locked
How to convert a date/time from UTC to local (CET) considering DST RRS feed

  • Question

  • Hi,

    I hope that I located the correct forum to ask my question.

    I am importing data from Dynamics CRM online using ODATA in Power Query. All datetimes in the database is stored in UTC and I need to convert these into CET. But it's not as simple as just to add 1 hour to the datetime from the database because due to Daylight Savings time on half the dates I need to add 2 hours - how can I do this?

    Kind Regards,

    Søren


    Regards, Søren Damgaard Jensen Senior BI Consultant @ Norriq, DK

    Tuesday, October 27, 2015 2:34 PM

Answers

  • Sweet... I was able to solve it with this code

    DateTimeZone.ToLocal( DateTimeZone.FromText( DateTime.ToText( [MyDateTime] ) & "+00:00" ) )


    Regards, Søren Damgaard Jensen Senior BI Consultant @ Norriq, DK

    Tuesday, October 27, 2015 3:08 PM

All replies

  • with SQL Server 2008 + versions

     

    SELECT CONVERT(datetime,

                   SWITCHOFFSET(CONVERT(datetimeoffset,

                                        MyTable.UtcColumn),

                                DATENAME(TzOffset, SYSDATETIMEOFFSET())))

           AS ColumnInLocalTime

    FROM MyTable

     

    With Less efforts:

     

    SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)

           AS ColumnInLocalTime

    FROM MyTable

    -Kiran


    Tuesday, October 27, 2015 2:41 PM
  • I am using power Query, so I need m formula and not SQL code, but thanks anyway

    Regards, Søren Damgaard Jensen Senior BI Consultant @ Norriq, DK

    Tuesday, October 27, 2015 2:44 PM
  • Sweet... I was able to solve it with this code

    DateTimeZone.ToLocal( DateTimeZone.FromText( DateTime.ToText( [MyDateTime] ) & "+00:00" ) )


    Regards, Søren Damgaard Jensen Senior BI Consultant @ Norriq, DK

    Tuesday, October 27, 2015 3:08 PM