BULK MSSQL IMPORTING DATE-ERROR

[Anonymous]

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?

[Jesperrekuh]

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.

[Anonymous]

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.

[Jesperrekuh]

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?

[Anonymous · Accepted solution]

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

[Anonymous]

Thank you so much sir.

[Anonymous]

Happy to help you.

Enjoy programming in Talend 🙂

Warm Regards,

Nikhil Thampi