topic Re: reject invalid date in Talend Studio

reject invalid date

rishind — Fri, 15 Nov 2024 22:22:22 GMT

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

Re: reject invalid date

Anonymous — Fri, 11 Nov 2022 08:13:04 GMT

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

Re: reject invalid date

rishind — Mon, 14 Nov 2022 09:37:33 GMT

Hi Shaung,

I want to reject invalid record while reading file.

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

Re: reject invalid date

Anonymous — Tue, 15 Nov 2022 07:16:15 GMT

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

Re: reject invalid date

rishind — Tue, 15 Nov 2022 09:18:51 GMT

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

Re: reject invalid date

Anonymous — Tue, 15 Nov 2022 10:29:00 GMT

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

Re: reject invalid date

rishind — Tue, 15 Nov 2022 13:28:33 GMT

In tmap expression filter

Re: reject invalid date

Anonymous — Wed, 16 Nov 2022 02:33:59 GMT

try this

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

Re: reject invalid date

rishind — Wed, 16 Nov 2022 14:24:13 GMT

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!!

Re: reject invalid date

Anonymous — Thu, 17 Nov 2022 07:37:09 GMT

Great, thanks for your feedback!