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

Date-time value into SQL table stopped working

Hello

 

Wonder is anyone is able to help with this one.  My flow has been working without issue for the past couple of months, but has recently stopped working.  

 

The purpose of the flow is to take rows from an updated Excel table, then create a row in a SQL table using the SQL connector.  The date-time value from Excel is causing the failure when trying to pass into the SQL add row function, with the error "We cannot convert the value \"4/22/2020 7:33:06 PM\" to type DateTime."

 

The starting point is an Excel column called "Date_Created"

Excel.JPG

The start of the flow, up to the SQL connector is as below:

Flow 1.JPG

 

As you can see, Flow is identifying the Excel content as a date-time value in the input section of the SQL function.  The yyyy-mm-ddThh:mm:ss recorded in Excel is converted in flow to the value shown below:

Flow 2.JPG

 

However, this then fails, with the error message as below:

Flow 3.JPG

"We cannot convert the value \"4/22/2020 7:33:06 PM\" to type DateTime."

 

This started to fail at the start of this week, but had previously worked.  

 

Any help would be much appreciated.

 

Thanks
John

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Date-time value into SQL table stopped working

Hello @johnwatson 

I think that however the datetime is presented internally in flow doesn't matter. The important part is for you to get the correct value/format when using the datetime in the SQL statement, correct?

 

If correct, then use the expression - "formatDateTime(<your datetime value>,'yyyy-MM-ddTHH:mm:ss') - which will ensure that the datetime is in the expected format for use in the SQL statement. Ps. correct the format as needed 🙂

Does the above help?

Kind regards, John

View solution in original post

6 REPLIES 6
Highlighted
Super User II
Super User II

Re: Date-time value into SQL table stopped working

Hello @johnwatson 

Could you check in one of the runs that previously worked and tell us whether or not the datetime from Excel is converted automatically to the same format as you now see?

Kind regards, John 🙂

Highlighted
Frequent Visitor

Re: Date-time value into SQL table stopped working

Hi @JohnAageAnderse 

 

Sure, no problem.  Here's the details from a successful run (17 April 2020).

 

First, the output from "Excel - List rows present in a table"

Working Flow 1.JPG

 

This then flows into the SQL insert rows block as input, and retains the same format through to the output (with the addition of the 'Z' at the end of the date).

Working Flow 3.JPG

 

Interestingly, you'll see that this is different to the failed runs that I'm now seeing.  Looking at one of the failed runs, below is the output from the Excel list rows function.  You'll notice the date format has now changed.  

Failed Flow Excel.JPG

 

There hasn't been any change in the underlying spreadsheet.  The relevant cell is stored as a general string with text, for example,  2020-04-22T19:33:06.

 

Perhaps there has been a change in the way the Excel connector handles dates?

 

Best wishes

John

 

 

Highlighted
Super User II
Super User II

Re: Date-time value into SQL table stopped working

Hello @johnwatson 

I am not sure what could be the reason for the change, but could you check the regional settings for the Excel and for the account that runs the flow?

Maybe one of them has changed - post which ones each has 🙂

Kind regards, John

Highlighted
Frequent Visitor

Re: Date-time value into SQL table stopped working

Hi @JohnAageAnderse 

 

Below are the regional/language setting on the flow account and in Excel.  Initially the details were blank.  I've populated them now (again as below), but the error is still occurring.

 

Flow setting were:

Flow Settings.JPG

 

Flow settings changed to (same problem):

Flow Settings changed to.JPG

Excel settings:

Excel Options.JPG

 

I'm not aware of any changes to the language / regional settings. 

 

I've tested a workaround this morning.  The steps I took were:

  • Made a copy of the flow
  • Edited the underlying data set in Excel.  Rather than being a recognised Date-Time format of yyyy-mm-ddThh:mm:ss (as text string), I've changed the string to be prefixed with a letter "D"... ie "Dyyyy-mm-ddThh:mm:ss".
  • When this is received into Flow it is left as a text string rather than Flow converting to a date (which seems to be the issue).
  • Then when passing into SQL, I've changed all of the date-time fields to be an expression which removes the prefixed "D"

 

replace(items('Apply_to_each_-_HEADERS')?['Date_Created'],'D','')

 

  • That leaves the ISO format date-time of yyyy-mm-ddThh:mm:ss which goes into SQL without issue.

While it isn't ideal, as doesn't solve what has changed and caused the malfunction and curious behaviour in Flow, it does get the process running again for now. 

 

All the best, 

John

Highlighted
Super User II
Super User II

Re: Date-time value into SQL table stopped working

Hello @johnwatson 

I think that however the datetime is presented internally in flow doesn't matter. The important part is for you to get the correct value/format when using the datetime in the SQL statement, correct?

 

If correct, then use the expression - "formatDateTime(<your datetime value>,'yyyy-MM-ddTHH:mm:ss') - which will ensure that the datetime is in the expected format for use in the SQL statement. Ps. correct the format as needed 🙂

Does the above help?

Kind regards, John

View solution in original post

Highlighted
Frequent Visitor

Re: Date-time value into SQL table stopped working

Hi @JohnAageAnderse 

 

That's correct, I agree it should just be a case of getting the formatting right. 

 

I've implemented your suggestion, using expressions such as:

formatDateTime(items('Apply_to_each_-_HEADERS')?['Date_Created'],'yyyy-MM-ddTHH:mm:ss')

 

After some initial frustrations where I was missing "items", pleased to say that it's working now.  

 

I did get some failures arising from some fields which can have null values.  For these fields, I've used the below alteration of the above:

if(equals(items('Apply_to_each_-_HEADERS')?['Date_Completed'], ''), '', formatDateTime(items('Apply_to_each_-_HEADERS')?['Date_Completed'],'yyyy-MM-ddTHH:mm:ss'))

 

All is working now.  Thank you very much for your help. 🙂

 

All the best,

John

 

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (8,214)