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

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
Highlighted
Community Support
Community Support

Re: Date conversion error with Excel Business & Sql Insert

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.
Highlighted
Advocate II
Advocate II

Re: Date conversion error with Excel Business & Sql Insert

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

Highlighted
Advocate II
Advocate II

Re: Date conversion error with Excel Business & Sql Insert

Hi Mable , any thoughts on this - thanks !

 

Highlighted
Community Support
Community Support

Re: Date conversion error with Excel Business & Sql Insert

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

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

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,019)