Forum Discussion

optiwatt's avatar
optiwatt
Frequent Visitor
3 years ago

How to display DateTime with TimeZone in table

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,

Need Help

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.

  • optiwatt's avatar
    optiwatt
    Frequent Visitor
    3 years ago

    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.. 😞