Date-time value into SQL table stopped working

[johnwatson]

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"

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

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:

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

"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

[JohnAageAnderse]

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 🙂

[johnwatson]

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"

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

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.  

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

[JohnAageAnderse]

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

[johnwatson]

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 changed to (same problem):

Excel settings:

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

[JohnAageAnderse · Accepted solution]

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

[johnwatson]

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