cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
djoeg01
Level: Powered On

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

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.
djoeg01
Level: Powered On

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

djoeg01
Level: Powered On

Re: Date conversion error with Excel Business & Sql Insert

Hi Mable , any thoughts on this - thanks !

 

Community Support Team
Community Support Team

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,904)