Search code examples
javasqlsnowflake-cloud-data-platformtalend

Talend - Issue with comparing dates using TalendDate routines

Here some context : I'm trying to retrieve a table from a Snowflake DB and store it in another one (ODS -> DWH) using Talend (Talend Cloud API Services platform 7.3.1) .one of the transformation I have to apply is to set the dates that have the value 1753-01-01 to null.

So for the datatypes :

  • input ODS : LETTER_DATE DATE
  • output DWH : DT_LETTER DATE

So I tried several methods to check the date and give the expected output :

The first method (TalendDate routines) :

TalendDate.compareDate(row2.LETTER_DATE, TalendDate.parseDate("yyyy-MM-dd", "1753-01-01")).equals(0) ? null : row2.LETTER_DATE

The second method :

row2.LETTER_DATE.equals(TalendDate.parseDate("yyyy-MM-dd", "1753-01-01")) ? null : row2.LETTER_DATE

Neither of this method worked for the first one I had '1' as a result instead of 0 and for the second I had 'false' I can't see if there something wrong with what I did or missed something (probably :) ). And so the data with the value '1753-01-01' passes.

So to try my ODS table is build like this :

CREATE OR REPLACE TABLE <TABLE_NAME> (

ID VARCHAR(50) NOT  NULL,

...,

LETTER_DATE DATE

);

And here is the data I'm trying to transform :

INSERT INTO <TABLE_NAME> (ID, LETTER_DATE) VALUES ('1999', '1753-01-01');

Thanks,

Solution

  • The Date type in java stores the date and time, so if you compare it against a date like 1753-01-01, the comparison may not work as expected as the time is not necessarily the same for both.
    I recommend you compare strings instead of dates:

    "1753-01-01".equals(TalendDate.formatDate("yyyy-MM-dd", row2.LETTER_DATE)) ? null : row2.LETTER_DATE