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
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
Hi Mable , any thoughts on this - thanks !
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
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