Date conversion error with Excel Business & Sql Insert

[djoeg01]

Hello Flowsters.

Ive created a flow to extract records from excel "get a row"  and then insert into sql.  Im running into a date conversion issue on the SQL insert side. Im using [InvoiceDate] [date] NULL,  datatype on sql.

Ive checked out the other posts but cant seem to nail it down.  Ive tried changing column format on excel , tried adding a compose and Date layers inbetween but dont seem to be getting anywhere .

any help will be much appreciated.

thanks

[v-yamao-msft]

Hi @djoeg01 ,

Could you share more details on your flow and the error message?

When working with date in Excel online, the date will be converted to string, which is not a valid date and cannot be inserted into a date type column.

You need to convert the string into a valid date using function first.

I have set up a simple flow likes below for your reference.

I am going to get a row from Excel table, which contains a date column named as Start, then insert it into a date type field in SQL table.

The function I used to convert the date:

addDays('1899-12-30',int(body('Get_a_row')?['Start']),'yyyy-MM-dd')

Please take a try with it on your side.

Best regards,

Mabel

[djoeg01]

Hi Mabel,   thanks for the reply.  

I was sure your function was going to work, but it through an error , it might be because of the Body "get a Row" int ( i did change my excel field name to match in the function. this is what I used   addDays('1899-12-30',int(body('Get_a_row')?['InvoiceDate']),'yyyy-MM-dd')     should I have changed 'Get a Row'  to something else 

---

@v-yamao-msft wrote:

Hi @djoeg01 ,

Could you share more details on your flow and the error message?

When working with date in Excel online, the date will be converted to string, which is not a valid date and cannot be inserted into a date type column.

You need to convert the string into a valid date using function first.

I have set up a simple flow likes below for your reference.

I am going to get a row from Excel table, which contains a date column named as Start, then insert it into a date type field in SQL table.

The function I used to convert the date:

addDays('1899-12-30',int(body('Get_a_row')?['Start']),'yyyy-MM-dd')

Please take a try with it on your side.

Best regards,

Mabel

---

---

@v-yamao-msft wrote:

Hi @djoeg01 ,

Could you share more details on your flow and the error message?

When working with date in Excel online, the date will be converted to string, which is not a valid date and cannot be inserted into a date type column.

You need to convert the string into a valid date using function first.

I have set up a simple flow likes below for your reference.

I am going to get a row from Excel table, which contains a date column named as Start, then insert it into a date type field in SQL table.

The function I used to convert the date:

addDays('1899-12-30',int(body('Get_a_row')?['Start']),'yyyy-MM-dd')

Please take a try with it on your side.

Best regards,

Mabel

---

?

Here is my original flow with the conversion error

Here is the error I get when I tried your function....

Thanks !

Greg

[djoeg01]

Hi Mable , any thoughts on this - thanks !

[v-yamao-msft]

Hi @djoeg01 ,

It is a known issue that Date and time value from the Excel table will be converted into a string, which cannot be used in the following actions if you want to insert the value into a Date and time filed.

You need to convert the string into a date and time value using the following function:

addDays('1899-12-30',int(body('Get_a_row')?['Start']),'yyyy-MM-dd')

Note: Start in this function is the column name of a datetime column, in your case, you need to replace Start with InvoiceDate.

Please check the previous screenshot I provided and take a try with it on your side.

Best regards,

Mabel