ATTN: Ken & Arnd : Subtracting date/time to convert into

[its_me48]

Arnd & Ken,

I had to start a new topic (even though it is continuation of previous one) since the earlier topic was closed.
But I ran into a problem when the start and end dates/time are different..

Here is the logic I followed..
Stage variables
StartDate = ICONV(In.START_DATE[1,10],'D4-YMD')
StartTime = ICONV(In.START_DATE[12,8],'MTS')
EndtDate = ICONV(In.END_DATE[1,10],'D4-YMD')
EndTime = ICONV(In.END_DATE[12,8],'MTS')

Derivation
IF StartDate=EndDate THEN EndTime-StartTime ELSE (EndDate-StartDate)*86400 + (86400-StartTime) + EndTime

If the start & end date are same it works fine.. if they are different, it gives wrong duration time
Example:
START_DATE: 2005-10-03 23:10:00
END_DATE: 2005-10-04 01:10:00
The result with the above logic is: 93600

It should be 7200 (or 120 minutes). It is calculating 26 hours...when the time difference is 2 hours...
Could you help me fix this problem.

Thanks,
-Sam

[ray.wurlod]

If you think about it, you will see that the answer is derivable. There are 86400 seconds in a day. 86400 + 7200 = 93600. What you need to incorporate is a test that the end time is earlier in its day than the start time is in its day, and subtract 86400 in this case.

[ArndW]

My fault with the original formula, it should be

Code: Select all

IF StartDate=EndDate THEN EndTime-StartTime ELSE (EndDate-StartDate-1)*86400 + (86400-StartTime) + EndTime

[its_me48]

Thanks Arnd. Works like a charm.

Thanks to all who helped me resolve this issue.

-Sam

[kcbland]

Glad you solved your problem, but I don't believe you were using the supplied function at:
viewtopic.php?t=85788

I used your date values and got an answer of 7200 seconds so I assumed your "Attn: Ken and Arnd" really meant Arnd because you weren't using my posted function.