How to display DateTime with TimeZone in table

[optiwatt]

Hello,

I use a SQL Server database to store values. My datetime is in UTC. I want to show the date as locale ("Western Europe") and i'm using direct query (import is not an option).

I wanted to keep it simple so i created a view on my table and used the SQL "at time zone" function

DH AT TIME ZONE N'W. Europe Standard Time'

 Works pretty well and in power query i got my datetime with the correct offset.

But in the report, i want to see the offset applied and i'm unable to find how to achieve this.

Example in the following screenshot, i want to show "25/07/2023 17:00:00" and not "25/07/2023 15:00:00" :

Any hints?

Thanks a lot!

Best regards,

[optiwatt]

Hello yanjiang,

Thanks for your reply!

In my country we have a daylight saving in summer/winter so sometimes it's duration + 1 and sometimes it's duration +2 so your solution won't work for my use case.. 😞

[v-yanjiang-msft]

Hi @optiwatt ,

I create a DirectQuery sample to test. If you already know the offset is 2 hours, you can directly add a custom column:

[Time]+#duration(0,2,0,0)

Get the result:

Then remove the original column, get the correct datetime in Desktop:

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.