cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Format data from sql server query

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....

 

2020-11-02_11-48-35.jpg

1 ACCEPTED SOLUTION

Accepted Solutions

Hello @rpiazza 

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

output.png

The expression to format the date is:

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

 

 

The result is the following:

output_html.png

 

Hope it helps!

Ferran

 

 

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

8 REPLIES 8
Frequent Visitor

Here is the data from the Sql odata call

 

20202-11-02T00:00:00Z

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.

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)

22222020-11-02_13-45-15.jpg

Hello @rpiazza 

You should write the expression inside the value field:

subs.png

 

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

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

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

 

2020-11-03_7-58-10.jpg

 

22020-11-03_7-59-41.jpg

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

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

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

JSON2020-11-03_16-52-30.jpg

Hello @rpiazza 

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

output.png

The expression to format the date is:

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

 

 

The result is the following:

output_html.png

 

Hope it helps!

Ferran

 

 

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (11,417)