Five Stars

BULK MSSQL IMPORTING DATE-ERROR

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Employee

Re: BULK MSSQL IMPORTING DATE-ERROR

Hi,

 

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

image.png

 

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

6 REPLIES
Nine Stars

Re: BULK MSSQL IMPORTING DATE-ERROR

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.
Five Stars

Re: BULK MSSQL IMPORTING DATE-ERROR

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.

Nine Stars

Re: BULK MSSQL IMPORTING DATE-ERROR

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?
Employee

Re: BULK MSSQL IMPORTING DATE-ERROR

Hi,

 

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

image.png

 

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

Five Stars

Re: BULK MSSQL IMPORTING DATE-ERROR

Thank you so much sir.
Employee

Re: BULK MSSQL IMPORTING DATE-ERROR

Happy to help you.

 

Enjoy programming in Talend :-)

 

Warm Regards,

 

Nikhil Thampi