DirectQuery SQL Query UTC to Swedish time

[lukasjar]

Hello.

I have a database that has Timestamps written in UTC. Is it possible to write a SQL query so that it always converts these to Swedish time (also winter/summer time. Adding +"01:00:00" will not work as this does not take account for summer/wintertime).

I also have a query that just gives me the StartOfHour from the Timestamp above. Is it possible to write this with the same rules as above.

format(TimeTrue, 'HH') StartTimeHour

Thank you for any assistance on this!

[lukasjar · Accepted solution]

https://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select... 

Found this now when searching the web. Following code works for system time.

TimeTrue is my column name.

SELECT TOP 10
TimeTrue,
CONVERT(datetime, 
SWITCHOFFSET(CONVERT(datetimeoffset, 
TimeTrue), 
DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
AS ColumnInLocalTime

FROM PerformanceOpc (NOLOCK)
WHERE convert(varchar,TimeTrue,23)>='2021-02-22'

[v-kelly-msft]

Hi  @lukasjar ,

Using below M codes to realize it:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTimeZone.SwitchZone(DateTime.AddZone([Column1],0),1))

And you will see:

For the sample .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[lukasjar]

Sorry Kelly but I can't add custom columns in direct query. I need to load the data in swedish time in the SQL query.