Handling Dates while copying from Excel to SQL server database table in Talend

Question

I am copying data from an Excel workbook to the SQL server database table. One of the column in my Excel sheet has Dates, which I am generating using Rand function.

If I keep data types of my columns as string in both my Excel input and SQL output. I get an error which says "Conversion failed when converting character string to smalldatetime data type."

If I keep my datatypes as Date it gives as error which says "The cell format is not Date in ....(cell address)"

In my table's definition in SQL server. It has data type as 'smalldatetime'.

To solve this problem I am using tConvertType. But I am getting an error which says: "The cell format is not Date in ....(cell address)"

I have attached my job's screenshots.

Answer 1

you can also use TalendDate.parseDate method for conversion... this can be used in expression in tMap..so your flow with reading date from excel as String and converting it to date using TalendDate.parseDate("yyyy-MM-dd HH:mm:ss","")

input -> tmap -> tmssqloutput components.

Answer 2

You need to convert the type in your Talend job.

Read the date in as a string and then use a tConvertType component to convert the type from string to a date type with a specific date format. You should then be able to send this to the database as a smalldatetime data type