Originally posted by: Haimame

Hi,

Can any one help me, how to find the date difference in WTX.
For example : In my scenario, the deliverydatetime will be given in the input in the format CCYYMMDDHH24MMSS. I need to find out the difference between the delivery date and the currentdate(). The difference may be in minutes or Hours or days.
But how to find the difference? There is no direct functions available in WTX.

Can anybody suggest me how to find that out?

Thanks in Advance.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: JGibby

Yeah, I think I have something that will work for you. Elapsed time is something that I use quite a bit. I have taken the formula and tweaked it a bit for you. The output shows the difference in Days, Hours, Minutes & Seconds ( D HH24:MM:SS). If you need a different output format, you could probably take this and tailor it to your needs.

Note that my formula takes two DateTime objects (DateIn and Current). It will determine which one is greater and do the calculations appropiately.

John

=IF( Current > DateIn 
// CurrentDateTime is greater than DateIn ,NUMBERTOTEXT( DATETONUMBER(Current) - DATETONUMBER(DateIn) - IF( MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),9,10) < MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),9,10) ,1 ,0 ) ) + 
" " +FILLLEFT( NUMBERTOTEXT( TEXTTONUMBER(MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),9,2)) - TEXTTONUMBER(MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),9,2)) 
// Cross days check / borrow 24 hours from days + IF( MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),9,10) < MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),9,10) ,24 ,0 ) 
// Cross hours check / will need to loan 1 hour to minutes  - IF( MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),11,8) < MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),11,8) ,1 ,0 ) ) ,
"0" ,2 ) + 
":" 
// Minutes ------------------------------------------------------------------------------ +FILLLEFT( NUMBERTOTEXT( TEXTTONUMBER(MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),11,2)) - TEXTTONUMBER(MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),11,2)) 
// Cross minutes check / borrow 60 minutes from hours + IF( MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),11,6) < MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),11,6) ,60 ,0 ) 
// Cross hours check / will need to loan 1 hour to seconds - IF( MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),13,8) < MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),13,8) ,1 ,0 ) ) ,
"0" ,2 ) + 
":" 
// Seconds ------------------------------------------------------------------------------ + FILLLEFT( NUMBERTOTEXT( (TEXTTONUMBER(MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),13,2))) - (TEXTTONUMBER(MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),13,2))) 
// Cross minutes check / borrow 60 seconds from minutes + IF( MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),13,2) < MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),13,2) ,60 ,0 ) ) ,
"0" ,2 ) 
// DateIn is greater than CurrentDateTime  ,NUMBERTOTEXT( DATETONUMBER(DateIn) - DATETONUMBER(Current) - IF( MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),9,10) < MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),9,10) ,1 ,0 ) ) + 
" " +FILLLEFT( NUMBERTOTEXT( TEXTTONUMBER(MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),9,2)) - TEXTTONUMBER(MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),9,2)) 
// Cross days check / borrow 24 hours from days + IF( MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),9,10) < MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),9,10) ,24 ,0 ) 
// Cross hours check / will need to loan 1 hour to minutes  - IF( MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),11,8) < MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),11,8) ,1 ,0 ) ) ,
"0" ,2 ) + 
":" 
// Minutes ------------------------------------------------------------------------------ +FILLLEFT( NUMBERTOTEXT( TEXTTONUMBER(MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),11,2)) - TEXTTONUMBER(MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),11,2)) 
// Cross minutes check / borrow 60 minutes from hours + IF( MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),11,6) < MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),11,6) ,60 ,0 ) 
// Cross hours check / will need to loan 1 hour to seconds - IF( MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),13,8) < MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),13,8) ,1 ,0 ) ) ,
"0" ,2 ) + 
":" 
// Seconds ------------------------------------------------------------------------------ + FILLLEFT( NUMBERTOTEXT( (TEXTTONUMBER(MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),13,2))) - (TEXTTONUMBER(MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),13,2))) 
// Cross minutes check / borrow 60 seconds from minutes + IF( MID(FROMDATETIME(DateIn,
"{CCYYMMDDHH24MMSS}"),13,2) < MID(FROMDATETIME(Current,
"{CCYYMMDDHH24MMSS}"),13,2) ,60 ,0 ) ) ,
"0" ,2 ) )

#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: jvanboga

a = sum(tonumber(fromdatetime(currentdate(), "ddd"))-tonumber(fromdatetime(datetimein, "ddd")))

b = sum(tonumber(fromdatetime(currentdatetime(),"hh24mm"))- tonumber(fromdatetime(datetimein, "hh24mm")))

c ="date variance = " + text(a)+ ", hour variance = " + text(int(sum(b/60))) + ", minute variance = " + text(b)
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: Haimame

Thanks alot guys..
I have got an idea now...The solution provided by u guys is really helpful to solve my issue.

GreatWork!
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender