topic Re: Convert DATE (date and time) to STRING (date and time) in Talend Studio

Convert DATE (date and time) to STRING (date and time)

joe86 — Wed, 28 Nov 2018 07:04:33 GMT

Hi All,

I need to convert a DATE to STRING with a condition.

I need to check if the incoming date (date with timestamp) is lesser than (current date + 7) days. If yes, then i need to pass a string like "To Be Expired" Else, I need to retain the incoming date (date with timestamp) value as it is. My incoming date column is of DateType DATE.

Can someone please help me to achieve this in tMap??

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Wed, 28 Nov 2018 07:31:56 GMT

@joe86,you can compare this way in tmap.

TalendDate.compareDate(TalendDate.addDate(TalendDate.getCurrentDate() ,7,"dd") ,row2.date1)==1 ? "To Be Expired" :TalendDate.parseDate("dd/MM/yyyy", row2.date1)

Re: Convert DATE (date and time) to STRING (date and time)

joe86 — Wed, 28 Nov 2018 07:49:03 GMT

thanks @manodwhb
Let me try this. if there is a difference in the format of the date in "row2.date1", will this comparision work. I am asking this since the date is coming from MongoDB and i am comparing it with "current date + 7". ??

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Wed, 28 Nov 2018 07:55:22 GMT

@joe86,what date format are you getting form Mongo DB?

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Thu, 29 Nov 2018 12:50:07 GMT

@joe86,still do you have issue?

Re: Convert DATE (date and time) to STRING (date and time)

joe86 — Fri, 30 Nov 2018 06:16:35 GMT

@manodwhb

Hi, sorry for delay. I am getting error as below:

can you please help on this??

Re: Convert DATE (date and time) to STRING (date and time)

joe86 — Fri, 30 Nov 2018 06:21:38 GMT

also, do i need to change the data type of this date column to "string" after applying this formula??

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Fri, 30 Nov 2018 06:43:00 GMT

@joe86,yes the output data type is sting. row2.date1 id date format.

TalendDate.compareDate(TalendDate.addDate(TalendDate.getCurrentDate() ,7,"dd") ,row2.date1)==1 ? "To Be Expired" :TalendDate.parseDate("dd/MM/yyyy", row2.date1)

Re: Convert DATE (date and time) to STRING (date and time)

joe86 — Fri, 30 Nov 2018 06:43:20 GMT

Hello @manodwhb,
please ignore all my previous.
in your command, you re checking if the incoming date is equal to (current date + 7). But i need, if the incoming date is LESSER THAN (current date + 7), the i need to display the date as it is. And if the incoming date is GREATER THAN (current date + 7), then i need to display "Will expire in 1,2,3 days".

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Fri, 30 Nov 2018 06:46:54 GMT

@joe86,i am checking that the incoming date is lesser than the current+7 days ,since i was using "==1" in the expresion.

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Fri, 30 Nov 2018 06:49:01 GMT

@joe86,then use the below way.

TalendDate.compareDate(TalendDate.addDate(TalendDate.getCurrentDate() ,7,"dd") ,row2.date1)==-1 ? "To Be Expired" :TalendDate.parseDate("dd/MM/yyyy", row2.date1)

Re: Convert DATE (date and time) to STRING (date and time)

joe86 — Fri, 30 Nov 2018 06:51:18 GMT

thanks for your patience. But still i am getting the same error.
I have changed my tMap output data type of date column to string, and getting same error again.

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Fri, 30 Nov 2018 07:03:31 GMT

@joe86, use the below one

TalendDate.compareDate(TalendDate.addDate(TalendDate.getCurrentDate() ,7,"dd") ,row2.date1)==-1 ? "To Be Expired" :TalendDate.formatDate("dd/MM/yyyy", row2.date1)

Re: Convert DATE (date and time) to STRING (date and time)

joe86 — Fri, 30 Nov 2018 07:11:31 GMT

hello,

still facing the same error. see below the screenshot on how i have given in tMap.

I have changed the data type to string also.

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Fri, 30 Nov 2018 07:19:06 GMT

@joe86,in the expression else part use the TalendDate.formatDate("dd/MM/yyyy", row2.date1)

Re: Convert DATE (date and time) to STRING (date and time)

joe86 — Fri, 30 Nov 2018 07:26:18 GMT

Thanks a lot dude. Its working now.
But a small issue.. If the incoming date is NULL/Blank, still the output is getting populated as "to be expired" instead of Null/Blank.

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Fri, 30 Nov 2018 07:29:57 GMT

@joe86, handle below way for nulls.

row2.date1==null ?row2.date1 : TalendDate.compareDate(TalendDate.addDate(TalendDate.getCurrentDate() ,7,"dd") ,row2.date1)==-1 ? "To Be Expired" :TalendDate.formatDate("dd/MM/yyyy", row2.date1)

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Fri, 30 Nov 2018 07:31:25 GMT

@joe86,please accept the solution and provide Kudos also

Re: Convert DATE (date and time) to STRING (date and time)

joe86 — Fri, 30 Nov 2018 07:43:24 GMT

i gave like below since the date column data type is string in target side:
Organiser.CRBExpiryDate== null ? TalendDate.formatDate("dd/MM/yyyy", Organiser.CRBExpiryDate):TalendDate.compareDate((TalendDate.addDate(TalendDate.getCurrentDate(),7,"dd")) ,Organiser.CRBExpiryDate)==1 ? "To Be Expired" :TalendDate.formatDate("dd/MM/yyyy", Organiser.CRBExpiryDate)

but getting NULL pointer exception error.

Re: Convert DATE (date and time) to STRING (date and time)

manodwhb — Fri, 30 Nov 2018 07:53:21 GMT

@joe86,

you should change below way.

Organiser.CRBExpiryDate== null ? Organiser.CRBExpiryDate : TalendDate.compareDate((TalendDate.addDate(TalendDate.getCurrentDate(),7,"dd")) ,Organiser.CRBExpiryDate)==1 ? "To Be Expired" :TalendDate.formatDate("dd/MM/yyyy", Organiser.CRBExpiryDate)