Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

Date conversion error with Excel Business & Sql Insert

(0) ShareShare
ReportReport
Posted on by 38

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 Profile Picture
    v-yamao-msft on at
    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  

     

  • djoeg01 Profile Picture
    djoeg01 38 on at
    Re: Date conversion error with Excel Business & Sql Insert

    Hi Mable , any thoughts on this - thanks !

     

  • djoeg01 Profile Picture
    djoeg01 38 on at
    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

  • v-yamao-msft Profile Picture
    v-yamao-msft on at
    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

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,495

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,822

Leaderboard

Featured topics