topic Compare two dates in Talend Studio

Compare two dates

muralam — Thu, 05 Apr 2018 12:03:20 GMT

hi Team,

i wanted to compare two date columns coming from two tables

i used TalendDate.compareDate(date1,date2) != 0 as it is returning an integer value this is not expected result

getting null pointer exception in tmap, please help on it.

Regards,

Meena

Re: Compare two dates

Anonymous — Thu, 05 Apr 2018 13:16:14 GMT

What is the expected result? Can you show me the tMap settings?

Re: Compare two dates

muralam — Thu, 05 Apr 2018 13:56:41 GMT

i need result like dd-MM-yyyy formate

Re: Compare two dates

muralam — Thu, 05 Apr 2018 14:02:07 GMT

In tmap output side filter, i gave like
TalendDate.compareDate(row1.date1,row2.date2) != 0
if result is equal i should not load anything
if result not equal i should load row2.date2
date1 nulls are expected, am comparing null=date2, i feel that can be the reason am getting null pointer exception in tmap, but could not able to solve it its very urgent i need to complete this asap

Re: Compare two dates

Anonymous — Thu, 05 Apr 2018 14:18:55 GMT

OK, in your tMap filter you need.....

TalendDate.compareDate(row1.date1,row2.date2) != 0

.....the nulls are handled by the routine method.

I'm guessing that your tMap output schema has the date column set to not nullable. I suspect that row2.date2 is null when it fails. However I will need to see the tMap config to tell if this is true

Re: Compare two dates

manodwhb — Thu, 05 Apr 2018 14:45:39 GMT

@muralam,first handale the nulls for bothe dates and comprare so that you will not get null pointer exception.

Re: Compare two dates

Anonymous — Thu, 05 Apr 2018 15:09:32 GMT

@manodwhb nulls are handled by the method.

    /**
     * compare two date
     *
     * @param date1 (first date)
     * @param date2 (second date)
     * @param pattern (compare specified part, example: "yyyy-MM-dd")
     * @return the result wheather two date is the same, if first one less than second one return number -1, equlas
     * return number 0, bigger than return number 1. (can compare partly)
     *
     * {talendTypes} Integer
     *
     * {Category} TalendDate
     *
     * {param} date(myDate) date1 : the first date to compare
     *
     * {param} date(myDate2) date2 : the second date to compare
     *
     * {param} String("yyyy-MM-dd") pattern : compare specified part
     *
     * {examples}
     *
     * ->> compareDate(2008/11/24 12:15:25, 2008/11/24 16:10:35) return -1
     *
     * ->> compareDate(2008/11/24 16:10:35, 2008/11/24 12:15:25) return 1
     *
     * ->> compareDate(2008/11/24 12:15:25, 2008/11/24 16:10:35,"yyyy/MM/dd") return 0 #
     */
    public static int compareDate(Date date1, Date date2, String pattern) {
        if (date1 == null && date2 == null) {
            return 0;
        } else if (date1 != null && date2 == null) {
            return 1;
        } else if (date1 == null && date2 != null) {
            return -1;
        }

        if (pattern != null) {
            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
            String part1 = sdf.format(date1), part2 = sdf.format(date2);
            return (part1.compareTo(part2) >= 1 ? 1 : (part1.compareTo(part2) <= -1 ? -1 : 0));
        } else {
            long time1 = date1.getTime(), time2 = date2.getTime();
            return (time1 < time2 ? -1 : (time1 == time2 ? 0 : 1));
        }
    }

Re: Compare two dates

manodwhb — Thu, 05 Apr 2018 15:12:09 GMT

yes @rhall,then he shoul dnot get null pointer exception in tmap while comparing.it coud be other filed that he needs to handle nulls in tmap.

Re: Compare two dates

muralam — Thu, 05 Apr 2018 15:36:06 GMT

Yes both date columns are nullable column I am trying to compare null with date value

Re: Compare two dates

Anonymous — Thu, 05 Apr 2018 15:48:43 GMT

Can you send us the complete nullpointerexception error?

Re: Compare two dates

muralam — Thu, 05 Apr 2018 19:04:29 GMT

Hi All,

Now am not getting the error null pointer exception, but am not able to compare date columns properly

i have written the logic as below, the attached screen shot, am comparing two tables on below date columns which can be null, if result set is false like null!=datevalue i have to update my target table with date

out.DEV_INT_BIRTH_DATE!= null && row1.DEV_INT_BIRTH_DATE != null ?(TalendDate.compareDate(out.DEV_INT_BIRTH_DATE,row1.DEV_INT_BIRTH_DATE)==0 ?false : true) :true

Regards,

Meena

Capture.PNG

Re: Compare two dates

Anonymous — Thu, 05 Apr 2018 19:33:55 GMT

It depends on if it's a primitive type or not. You have to use Integer.equalsTo(Integer) if its an Integer type. "==" is for a primitive type of int.

Re: Compare two dates

Anonymous — Thu, 05 Apr 2018 19:35:25 GMT

Integer.intValue() == 0. In all these cases Integer is your comparison result.

Re: Compare two dates

muralam — Thu, 05 Apr 2018 20:26:41 GMT

But these are date columns I didn't get your input compare integer

Re: Compare two dates

Anonymous — Thu, 05 Apr 2018 22:15:49 GMT

Can you show your whole tMap please? I believe your problem is with your configuration, but I can't tell unless I can see your whole tMap. By the way, there is no need for you to write the code you have included above. This code...

TalendDate.compareDate(out.DEV_INT_BIRTH_DATE,row1.DEV_INT_BIRTH_DATE)==0

....will return true if the columns match (even if both are null....which is possibly where your problem arrises) or false if they do not. Nulls are handled here. This is all you require for your logic as to whether an update is required. If you can read Java, take a look at the routine yourself. I have posted the code in a message above.

Re: Compare two dates

muralam — Fri, 06 Apr 2018 03:03:38 GMT

@rhall
yes i have to handle nulls, may be i have to compare
null=datevalue
null=null
null=empty, when i compare as these are nullable columns, nulls are expected, which i am not getting the result as expected,
my expected result i wanted to compare null=date load date value, null=null,null=emptyfield load nothing pass null, finally i wanted to pass data when there is a date value or else null, kindly help me out.

Re: Compare two dates

muralam — Fri, 06 Apr 2018 03:26:58 GMT

The same above mentioned scenario i am able to load data in case of strings where i have written a small routine to compare the required columns to find updates

@rhall, i have given complete tmap settings

Regards,

Meena

tmap settings.PNG

Re: Compare two dates

muralam — Fri, 06 Apr 2018 07:58:38 GMT

Hi,

Any update on the below request, as it is little urgent

Regards,

Meenas

Re: Compare two dates

manodwhb — Fri, 06 Apr 2018 08:27:32 GMT

@muralam,please findthe attahced smaple job to compare dates.

test_date.zip

Re: Compare two dates

Anonymous — Fri, 06 Apr 2018 09:28:48 GMT

OK, please consider this as FACT, not fake news. This method will work and take into consideration nulls as well as dates.....

TalendDate.compareDate(out.DEV_INT_BIRTH_DATE,row1.DEV_INT_BIRTH_DATE)==0

So, if your values are null and null, it will return 0. If your dates are the same (down to the millisecond) it will return 0. If your dates are different or one is a null, then it will return either 1 or -1 depending on which date is null or greater.

If you are getting a nullpointerexception I guarantee that it is NOT to do with this method. This is just confusing the issue.

Can you please take a screenshot of your whole tMap. NOT small sections. I need to see ALL of it. Can you also add a tLogRow before the tMap (for both inputs, Main and Lookup) and post the data here so that we can see what is going into your tMap? You will also get an error message with the nullpointerexception. Can you post the exact error.