topic BULK MSSQL IMPORTING DATE-ERROR in Talend Studio

BULK MSSQL IMPORTING DATE-ERROR

Anonymous — Sat, 16 Nov 2024 07:51:38 GMT

I use this job to import data from a csv file into a bulk mssql component, I got an error on a date field in the table because the date is less than 1-1-1753, so I used an output file to catch the rejected lines but it doesn't catch any erroneous date.

any solution?

Re: BULK MSSQL IMPORTING DATE-ERROR

Jesperrekuh — Fri, 03 Aug 2018 11:17:56 GMT

It is a MSSQL error not a java error.
I believe the smallest date in java is Sun, 2 Dec 292269055 BC 16:47:04 +0000

Since the minimum date value that a DATETIME data type can have is January 1, 1753, you have to use either INT, DECIMAL/NUMERIC or VARCHAR data type to hold a date value before January 1, 1753. Of these three data types, the best data type to use is the INT data type because it will only require 4 bytes to store the data. DECIMAL or NUMERIC data type will require 5 bytes to store the date value in YYYYMMDD format while a VARCHAR data type will use 8 bytes.

Re: BULK MSSQL IMPORTING DATE-ERROR

Anonymous — Fri, 03 Aug 2018 11:43:23 GMT

THANK YOU SO MUCH FOR YOUR ANSWER,

but actually I want to reject dates before 1-1-1753,I don t want to keep them so I used the output file to reject them.

but I got error and the file doesn t catch errorenous rows.

even I use Tfilter it generates errors.

Re: BULK MSSQL IMPORTING DATE-ERROR

Jesperrekuh — Fri, 03 Aug 2018 11:52:15 GMT

If you talk about errors, please attach it...
Suggest using a tMap with 2 outputs and filter.

For the sake of argument... Why on earth you want to reject these records, because of technical mssql limitations?

Re: BULK MSSQL IMPORTING DATE-ERROR

Anonymous — Fri, 03 Aug 2018 12:22:08 GMT

Hi,

Please use the below filter expression in tmap to filter older dates.

TalendDate.compareDate(row1.data,TalendDate.parseDate("dd-MM-yyyy","01-01-1753")) == 1

I was able to segregate the data using this condition.

If the update has helped you, could you please mark the topic as solution provided?

Warm Regards,

Nikhil Thampi

Re: BULK MSSQL IMPORTING DATE-ERROR

Anonymous — Fri, 03 Aug 2018 13:26:49 GMT

Thank you so much sir.

Re: BULK MSSQL IMPORTING DATE-ERROR

Anonymous — Fri, 03 Aug 2018 13:43:41 GMT

Happy to help you.

Enjoy programming in Talend 🙂

Warm Regards,

Nikhil Thampi