reject invalid date

[rishind]

Hi ,

I am loading data from csv file to sqlserver DB.

I have a date filed(DOB) in YYYY-MM-DD format.

sometimes we receive date like 0001-09-03, though format is correct, its incorrect date and we get error from sqlserver like

" Exception in component tDBOutput_5

java.sql.SQLException: Only dates between January 1, 1753 and December 31, 9999 are accepted." and it stops the flow.

So how can I reject those records and continue the flow. I just wanted to move to reject file and continue the flow

can I do it using tschemacompliance or tmap?

I am using Talend DI 8

[Anonymous]

Hi

Uncheck the 'die on error' and the 'use batch size' boxes, then you are able to output the rejected data to a file.

...tMssqlOutput--reject--tFileOutputDelimited.

Regards

Shong

[rishind]

Hi Shaung,

I want to reject invalid record while reading file.

Is there any option in tfilelist to accept date between date range?

[Anonymous]

tFileList is used to iterate files rather than reading the file content, you can filter the data after reading the file, eg:

tFileInputDelimited--main--tFilterRow--main-->tDBOutput

***********************************************--reject--tFileOutputDelimited.

Regards

Shong

[rishind]

Hi,

I am rejecting records in tmap by filtering on date like

(TalendDate.getPartOfDate("YEAR",(row1.DOB))>=1753 && TalendDate.getPartOfDate("YEAR",(row1.DOB))<=9999)

but when it encounters null it treats as 0,so it filter out record where date is null.(assuming it to 0)

so I tried below

1)row1.DOB!=null?row1.DOB:(TalendDate.getPartOfDate("YEAR",(row1.DOB))>=1753 && TalendDate.getPartOfDate("YEAR",(row1.DOB))<=9999):row1.DOB

but it throws java null pointer execption error

so is there a way where it should apply above logic for only non null fields

[Anonymous]

Where do you write this expression? It seems the logic of your expression is wrong.

[rishind]

In tmap expression filter

[Anonymous]

try this

row1.DOB!=null&&(TalendDate.getPartOfDate("YEAR",(row1.DOB))>=1753 && TalendDate.getPartOfDate("YEAR",(row1.DOB))<=9999))

[rishind · Accepted solution]

I tried below and it worked

TalendDate.getPartOfDate("YEAR",(row1.DOB))>=1753 || TalendDate.getPartOfDate("YEAR",(row1.DOB))==0))

) because TalendDate.getPartOfDate is treating null as 0(DOB could be null also)

I cannot use!=null as there null records in DOB

Thanks for Support shong!!

[Anonymous]

Great, thanks for your feedback!