Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My date/time field is in EST in my SQL database but PBI switches it into UTC. I want it in EST, anyway to just keep it EST from the start instead of converting back and forth?
Solved! Go to Solution.
Hi @Anonymous ,
It is not supported yet currently. There is an idea about that you can vote it up to make this feature coming sooner.
BTW, you can create a custom column using DateTime.AddZone function to work around. This function adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.
DateTime.AddZone([CreatedOn],3)
Hi @Anonymous ,
It is not supported yet currently. There is an idea about that you can vote it up to make this feature coming sooner.
BTW, you can create a custom column using DateTime.AddZone function to work around. This function adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.
DateTime.AddZone([CreatedOn],3)
This is old, but still relevant, so I'll post this for anyone else who is still having this issue.
This relates to specifically to SQL server as the data source.
Essentially, you need to explicitly convert UTC times to your local timezone as part of the query for your table. It needs to be done for each date/time column. If you do not explicitly make the conversion, Power BI will display the dates in UTC time when uploaded to Power BI service.
The easiest way to do this is like this (change ParentBatchCreatedAt to your column name and obviously set your correct time zone):
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, ParentBatchCreatedAt), DATEPART(TZOFFSET,ParentBatchCreatedAt AT TIME ZONE 'New Zealand Standard Time'))) AS ParentBatchCreatedAt
Note: ParentBatchCreatedAt AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' does not work properly and will not help. It needs to be the one above.
Doing this lets Power BI know that an explicit conversion has been set on the column and to not touch it.
Longer example of query:
SELECT
DFtriggerType,
DFTriggerName,
ParentBatchID,
BatchDesc,
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, ParentBatchCreatedAt), DATEPART(TZOFFSET,ParentBatchCreatedAt AT TIME ZONE 'New Zealand Standard Time'))) AS ParentBatchCreatedAt,
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, ParentBatchClosedAt), DATEPART(TZOFFSET,ParentBatchClosedAt AT TIME ZONE 'New Zealand Standard Time'))) AS ParentBatchClosedAt,
BatchID,
CurrentLoadStatus,
ProcessName
FROM [ETL].[BatchDataLoadDetail]
If your dates are already converted in the source to your local time (not good practice when the server is on a different time zone i.e. UTC), you will need to convert the dates to UTC and then to your local time zone. I'd probably use a view to convert the dates to UTC and then in Power BI pull from that view with the local time zone conversion as above.
Hope this helps.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |