topic Re: How to split Date, Time field and apply a condition using expression function in Talend Studio

How to split Date, Time field and apply a condition using expression function

Anonymous — Sat, 16 Nov 2024 04:51:31 GMT

Dear All,

Using tmap, I am trying to get a Date and Time field (2018-04-07 12:53:55.557) from one table and trying to validate below conditional check to pass date and shift details to another table.

Condition for Date Field is as follows:

I need to take time from above field(12:53:55.557) and check for condition is it > '00:00:00.000' && < '06:00:00.000' if it so I need to subtract one from above date field and push to other table(2018-04-06) else I need to pass same date to that table(2018-04-07)

Condition for Sift Details are as follows:

I need to take time from above field((12:53:55.557) ) and need to check in which shift it belongs to and send the shift details(A,B,C) to another table.

Please find the attached screen shots for your reference and assist me how this can be solved using talend expression function.

Re: How to split Date, Time field and apply a condition using expression function

fdenis — Tue, 27 Aug 2019 09:52:35 GMT

did you have date or string?
have a look at "Java SimpleDateFormat" or use Talend Date Routines:
https://help.talend.com/reader/~R4Lk_SlELw9a8pKBKTm9A/JFWl8U3lEbhWZEFAUtYvMw

Re: How to split Date, Time field and apply a condition using expression function

Anonymous — Tue, 27 Aug 2019 10:12:34 GMT

Thank you for your update denis.

I have date field(row1.D_FIN_F) and its format is (2018-04-07 12:53:55.557). I have tried using the Talend Date routines - TalendDate.formatDate("yyyy-MM-dd", row1.D_FIN_F) but when i test it doesn't works. PFA

Talend_ExpressionTag1.JPG

Re: How to split Date, Time field and apply a condition using expression function

iamabhishek — Tue, 27 Aug 2019 11:07:49 GMT

@KathikVenky
The compare (.> & <) that you are trying to perform won't work in case of date or strings. You need to use

TalendDate.compareDate
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)

So, all you need to do is convert your '00:00:00.000' & '06:00:00.000' and perform the calculation and derive your flow.
Something like this -

TalendDate.parseDate("HH:mm:ss.SSS",TalendDate.formatDate("HH:mm:ss.SSS", splitdate1.splitDate))
TalendDate.parseDate("HH:mm:ss.SSS","00:00:00.000")
TalendDate.parseDate("HH:mm:ss.SSS","06:00:00.000")

--compare
(TalendDate.compareDate(Var.var1,Var.var2,"HH:mm:ss.SSS") >= 1 && TalendDate.compareDate(Var.var3,Var.var1,"HH:mm:ss.SSS") >= 1) ? 1 : 0

Re: How to split Date, Time field and apply a condition using expression function

fdenis — Tue, 27 Aug 2019 11:13:26 GMT

you may stay in date type.
creating new date from TO_DATE(TalendDate.formatDate("CCYY-MM-DD", row1.D_FIN_F),"CCYY-MM-DD")
use diffdate on hours to get h
then check if 0<h<6

Re: How to split Date, Time field and apply a condition using expression function

Anonymous — Wed, 28 Aug 2019 05:55:24 GMT

@iamabhishek Thank you very much.