Format data from sql server query

[rpiazza]

Hello and thanks in advance.

I have flow that queries a sql server table and returns data; in particular a date(Record_Added_DateTime).  Data ret can contain one row or multiple rows.  In the sql DB the date is formatted as YYYY-MM-DD without the time however the query returns the time as well.  Is there a way that I can remove the time portion?    Not sure what step I would format the date prior to emailing.  Was thinking the HTML step but not sure on the syntax.

Thanks Again....

[rpiazza]

Here is the data from the Sql odata call

20202-11-02T00:00:00Z

[Paulie78]

You could use formatdatetime to give you any output format you like:

https://support.microsoft.com/en-us/help/4534778/how-to-customize-format-date-and-time-values-in-a-f... 

Or in your case you could simply use a substring expression like:

substring(DateToChop, 0, 10)

which will give you the first 10 characters, which would be:

2020-11-02

(I was guessing you had a typo below, year 20202 makes no sense to me). You can put the substring expression directly into the HTML table. Using your SQL result as the input for the substring expression. Replace "DateToChop" with your value from SQL.

[rpiazza]

Thanks for responding,  Not sure if I set up the html substring correctly since it treated the whole function as a literal...see below  I to
I took out sql info.  Result is  substring(0,2020-01001T00:00:00Z,10)

[fchopo]

Hello @rpiazza 

You should write the expression inside the value field:

And the expression should be:

substring(date_string,0,10)

Where date_string is the date variable (Record_added_... in your case).

Hope it helps!

Ferran

[rpiazza]

Thanks Ferran,  that didn't work I think because its passing an array.

 

[fchopo]

Hello @rpiazza 

Could you share the output (json data) of the Get Rows action? In this way we will be able to see the type of data you want to manage, and why Records_Added... is an array and not a value.

Regards,
Ferran

[rpiazza]

I selected a diff col from the SQL DB Date_Time_Str....still the same result.

Att is the JSON data from the get rows.

Thanks

 

[fchopo · Accepted solution]

Hello @rpiazza 

I created a flow to test the format of the date column, and it is working. I attach a sample:

The expression to format the date is:

formatDateTime(item()?['Date_time_str'],'yyyy-MM-dd')

The result is the following:

Hope it helps!

Ferran