Timezone offset missing

[DPozzi]

For business requirements, we should write in a SQL column a date time in 'o' format included the UTC timezone offset value.

I supposed that CONVERT TIME ZONE action with round trip 'o' format could be the solution but I was wrong, it's output miss the timezone. I used a custom formatstring instead "yyyy-MM-ddTHH:mm:ss.fffffffzzz"

Timezone offset appears but it's +00:00 no matter what destination time zone is.

I would expect 2023-09-29T11:09:45.5042321+02:00 not +00:00 due to the 2-hour current offset of W.Europe Standard Time from UTC. 

Anyone could explain or solve?

PS:

I used also:

• formatDateTime(utcNow(),'yyyy-MM-ddTHH:mm:ss.fffffffzzz','it-IT')
• convertFromUtc(utcNow(),'W. Europe Standard Time','yyyy-MM-ddTHH:mm:ss.fffffffzzz')

but makes no difference at all the results are the same.

[efialttes]

@DPozzi 
I agree with you, looks like a bug. An ugly workaround could be this one:

Add also a new 'Condition' action block to handle +02.00 scenario, or forget Condition action blocks and use Switch action block instead.
Hope this helps
==========

[wskinnermctc · Accepted solution]

@DPozzi I have not been able to get the +02:00 to format with functions. However, there is a way to put together a string while still using system time.

This is not wildly different than the response provided by @efialttes Octopus Teacher 

This basically converts the time from Utc to your timezone like normal and uses the [s] sortable format "yyyy-MM-ddThh:mm:ss" and then combines it with output string of the datedifference() function.

Here is the example flow below:

Convert Time Zone and DateDifferenceResults Convert Time Zone and Date Difference

I don't know if the SQL server will have an issue with the additional seconds being added to the string "02:00" vs "02:00:00" . 

If so you can format the string to only get the first 6 characters or something.

[efialttes]

@wskinnermctc A much more elegant approach than mine. Thanx for sharing!
==================================================

[wskinnermctc]

Yeah, but it is still annoying that the Off Set time function basically doesn't work in Power Automate. You can't even use the drop down they provided in their connector. 

Like why have it available if it doesn't work properly?? (Or at least doesn't work intuitively, because I sure couldn't get it to work.)

My bootleg answer is cute, but it actually doesn't work if the timezone is elsewhere because there will be a - minus sign included in the output. So it would end up being +-02:00 which is an error. So it only works if you are for sure of the timezone the results will be.

[DPozzi]

I agree, I'm so bothered every time I find another power platform bug.. 

[efialttes]

@DPozzi
Just in case somebody else reads this thread and is affected by the -/+ issue mentioned by @wskinnermctc, here it is a possible improvement. Please also note you can even select several responses as solution, even from different community members!

This WDL expression will do the magic: 

take(if(startsWith(dateDifference(variables('inputDateTime'),body('Convert_time_zone')),'-'),dateDifference(variables('inputDateTime'),body('Convert_time_zone')),concat('+',dateDifference(variables('inputDateTime'),body('Convert_time_zone')))),6)

It works fine no matter we are working on UTC minus X hours...

and also if we are working on UT plus X hours:

I also agree such painpoints make things almost impossible for users with no previous programming experience, and difficult for user even with previous programming background

That's why people like @wskinnermctc make this community great
Thanx!
====