cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate V
Advocate V

Date conversion error with Excel Business & Sql Insert

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

 

 

4 REPLIES 4
Community Support
Community Support

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.

1.PNG

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

1.PNG

 

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.

1.PNG

 

Best regards,

Mabel

 




?

 

Here is my original flow with the conversion error

 

flow1.JPGflow3.JPG

 

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

 

flow4.JPG

 

Thanks !

 

Greg

Advocate V
Advocate V

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

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

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!

Users online (55,512)