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

[Anonymous]

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.

[fdenis]

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

[Anonymous]

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

[iamabhishek · Accepted solution]

@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 

[fdenis]

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

[Anonymous]

@iamabhishek Thank you very much.