Power Automate Writing UTC Date/Time to SQL As Local Time (How to Stop)

[J2K]

I have a flow that grabs the current date/time at UTC and writes it to a SQL database. For some reason, it is outputting the field converted to US Eastern Time (which is my local time).

I initialize a variable as utcnow():

Which returns correctly:

I pass this variable to a SQL: Insert row (V2) step:

The INPUTS show the correct UTC time as in the variable:

For some reason, the OUTPUTS show it converted to US Eastern Time:

When I write a UTC time to the same SQL Server database directly from a Patch command in Power Apps, it goes in as the correct UTC. I need to be able to write to the db in UTC from a Flow as well.

Any ideas why this is happening?  Thanks.

[wskinnermctc]

What is interesting to me is that the "converted US Eastern Time" still ends with a 'Z'. So if you put that time back into PA for something else, it would read it as UTC. Except it would be incorrect since it would be saying 15:58 UTC instead of 19:50 UTC.

However, I know SQL and PA don't play friendly when it comes to time. 

What type of column are you inserting data into in the SQL Server? datetime? datetimeoffset?

I'm unable to insert anything into SQL Server to test. But I get things out of SQL Server, and I have found using a datetimeoffset type column will allow my datetimes to be used by PA correctly. It might be the same when inserting data? 

[spmofulton]

@wskinnermctcI would 100% use offset.  I can confirm that datatimeoffset should work.  

What is irksome is that utc() functions can get ignored depending on the computer configuration.  This happens a lot at work in python and R where we have our automation  machine on a hosted server hardware where everything is in EST.  So I would not be suprised if there is a windows configuration issue.
datatimeoffset is exactly what we use at work.

[J2K]

The column is [datetime2](7).

[J2K]

While you and @wskinnermctc are probably spot on with datetimeoffset working, my concern is how do I do that when this flow will be triggered by people in different time zones?  What am I using to offset?  I am in the East, but other users are not unfortunately.

[wskinnermctc · Accepted solution]

@J2K I have an idea, but you will have to test it since I can't. 

You will need the column in SQL Server to be a DateTimeOffset type of column. 

You are getting the current time as UTC. This can be stored in a DateTimeOffset column and it is identified as +00:00. 

So try to format the date time into what it should look like in the SQL Server column. To add an offset use 'zzz' at the end of a datetime format.

See how that looks going into SQL Server DateTimeOffset column.

I formatted the datetime as below:

yyyy-MM-dd HH:mm:ss.FFFFF zzz

You can adjust the fractions of the seconds with FF or ff however many you want. Date Format Strings  show them.

Here is an example below. See if you can get that put into your SQL Server column with the offset number. It doesn't matter what time zone your user made a submission since they will all stay in UTC +00:00.

The results are here:

Let me know how it goes and if PA automatically changes the time again. We might have to adjust some time conversion, but we'll see how it goes.

[J2K]

You're the best. I altered the SQL column to be datetimeoffset and without making any changes at all to my flow, it is now going in as UTC.  I will monitor it and get someone in a different time zone to test for me, but it looks like possibly all I had to do was set the SQL column itself to datetimeoffset.

If I encounter other issues, I will try the rest of your solution (changing the PA flow as well).

Thank you very much.