1

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. enter image description here

enter image description here

Quick-gun Morgan
  • 338
  • 12
  • 31

2 Answers2

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.

garpitmzn
  • 1,001
  • 6
  • 9
  • I have tried it but it still gives an error. Please see my screenshots here.. http://stackoverflow.com/questions/23920933/unparseable-date-error-talend/23927374#23927374 – Quick-gun Morgan May 29 '14 at 13:20
  • i have added more validation to that post also. please check – UmeshR May 29 '14 at 13:46
0

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

ydaetskcoR
  • 53,225
  • 8
  • 158
  • 177
  • Hey, my job has excel input -> tmap -> tmssqloutput components. where and how do I use tConvertType component? – Quick-gun Morgan May 28 '14 at 16:52
  • Will my new job flow be like: Excel Input -> tConvertType -> tMap -> tMSSqlOutput ? – Quick-gun Morgan May 28 '14 at 17:22
  • I have tried but it gives me an error which says "java.text.ParseException: Unparseable date: "Fri Nov 30 00:00:00 EST 2001" – Quick-gun Morgan May 28 '14 at 17:51
  • Can you edit you question to provide an example of the dates coming in and also a screenshot of your job as it is now? Also show the configuration of your tConvertType if possible (I'm assuming this is what's throwing the error here?) – ydaetskcoR May 28 '14 at 18:05
  • I have edited the question with the screenshot. https://www.youtube.com/watch?v=2TCmUs8_9uM I have kept setting as shown in this video. Note: even though I have selected Repository from the drop down for schema it gets changed to built-in. – Quick-gun Morgan May 28 '14 at 18:31
  • The error message you're seeing there seems to imply that you're still reading in column 18 as a date instead of a string – ydaetskcoR May 28 '14 at 19:36
  • Yes, I corrected them still I have an error which says: "java.text.ParseException: Unparseable date: "Tue Jul 17 00:00:00 EDT 1973" – Quick-gun Morgan May 28 '14 at 19:41
  • http://stackoverflow.com/questions/23920933/unparseable-date-error-talend Here I have posted the rephrased question. Please have a look. – Quick-gun Morgan May 28 '14 at 20:10