How to export a date/time field from SQL table such as "2020-08-03T00:00:00Z" to 8/3/2020 on a Google Sheet?

(0) Share

Report

Posted on by dc7669

152

I want to export a Date Field from a SQL data table to Google Sheet as Month/Day/Year.

The problem I have is the the Date Field is exported as "2020-08-03T00:00:00Z" to a Google Sheet, but I want it to export as "8/3/2020" or at least take out everything after the "T".

I can export Closing Date to GoogleSheet, but it will look like "2020-08-03T00:00:00Z".

So, I think I should use "Expression" such as "formatDateTime (ClosingDate, 'm/d/yyyy')" to change its format to m/d/yyyy.

Yet, when I typed in "formatDateTime in Expression, and then switch to "Dynamic content", the only options I have are "List of items" and "value". All the field names from the SQL table are gone.

How do I export a date/time field from SQL table such as "2020-08-03T00:00:00Z" to 8/3/2020 on a Google Sheet?

Thanks!

dchan

Categories:

Using flows

All responses (3)

Answers (2)

Sort by

Christopher S Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @dchan1

You can use the "Convert time zone" action form Flow in order to get the format that you wish:

On the format string field, you can select which format you wish.

Best Regards

--

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Verified answer

v-bacao-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @dchan1 ,

You could fill the corresponding dynamic content in the Compose and use formatDateTime() function to format the output of the Compose.

Then you could fill the following expression in the corresponding field.

formatDateTime(outputs('Compose'),'M/d/yyyy')

Hope this helps.

Best Regards,
Verified answer

dc7669 152 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @v-bacao-msft @chsanche 's inspirations. Your methods may work, but this is how I got it at the end.

I created a Test Variable to hold "ClosingDate" within the "Apply to each" loop for my SQL table.
Then, I created another variable, Test2, to hold the "correct value", i.e. formatDateTime(Test, 'M/d/yyyy')
Then, in the "Insert row" step, I put Test2 variable in, instead of the ClosingDate field from SQL table.

Looking back, it looks like a logical and sensible method. I wonder why I did not think of it before!

Thanks for your input.

Dchan