Failed to convert parameter value from a Double to a DateTime - Insert DB

Hi to all,
I have a problem importing a file to DB.
Test.xls (4,0 MB)

I need to upload this File to a database.
I created the database table, but I have a failure with the IMPORT command.

image

Depends on what? the date formatting looks correct.
Can anyone help me?
Bot_Credit_Managment.xaml (11,0 KB)

My table on db:


What am I doing wrong?
Thanks…
Aaron

Hey you have 05 date fields in your DB, make sure the excel that you are reading also gives you the fields in same date datatype.
else you loop thru the table and convert these five field into a date type before importing to DB

I ran thru your excel the date values are coming as OADate format Number as below, Do one simple thing replace the workbook read range activity with an Excel Application Scope read range the issue will get resolved.(2nd screenshot)

Hi Sarath,
unfortunately I can’t use the Excel application.
My remote server has no office so I need to find a workaround. If it exists.

That is,
are you telling me that the alternative is a Build Data Table and import the data through the For Each command?

Hey Use a For each row data table activity and convert the date fields to Date.

Use an Assign activity

Left Hand side > Row(“ColumnName”)

RHS >

Hi Sarath,
ok, i entered this string.
I don’t understand how to make it work with importing the table to DB though.
After converting the 5 data columns in this way, can I use the INSERT DATA TABLE command?

image

image

yes, once you convert all the date field to date type then the double data type issue will get resolved. Try that first and let’s see what comes after it :wink:

We are close.
The error is now: Data too long for column ‘Customer_Deactivation_Date’ at row 1",
Obviously I converted 10 characters for the date
and then added hh mm ss.
Can I possibly cut and take only the first 10 characters?

Left(datetime.FromOADate(cdbl(row(“Ends_Date_1B”))), 10)
could this make sense?

Or simply you can use Date.FromOADate instead of DateTime

Hi Sarath…
i solved the problem, maybe related to some formatting in the cells.
Now I have managed to import the whole file.
I hope it will not show up in the future, having to work independently.
Thanks for your invaluable help! :slight_smile:
The suggestion you gave me was decisive.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.