Subtracting date and converting into minutes

[its_me48]

I have 2 columns: START_DATE & END_DATE in the following format: 2005-12-02 18:40:00 (YYYY-MM-DD HH24:MM:SS)

I have a column called DURATION which is a number(decimal) field and here is the rule: END_DATE - START_DATE in MINUTES

So if a record has the following dates:
START_DATE: 2005-12-02 18:40:00
END_DATE: 2005-12-02 20:20:00
The result must be: 100 minutes (DURATION=100)

How do I do it in DataStage (preferably in transformer stage where I have both the start and end date)

Thanks !!

[kcbland]

Look here:

viewtopic.php?t=85788

[ArndW]

Hello its_me48,

even though this subject has been handled several times before, the way you posted the question was clearer than others, so I figure if an answer gets posted here it will be found by others using the "search" mechanism

Assuming the end date/time is always in the correct format and using Stage variables (for clarity, although you could program it differently):

Code: Select all

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')

This puts StartDate/EndDate into internal (julian days since 1970), and StartTime/EndTime into internal (seconds-since-midnight.)

The derivation for DURATION can then read:

Code: Select all

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

86400 is the number of seconds in a day (60*60*24)

[its_me48]

Ken,
Thanks for the quick reply...
Do I create a routine with the below code...

StartDate = LEFT(Arg1,10)
StartTime = RIGHT(Arg1,8)
FinishDate = Left(Arg2,10)
FinishTime = RIGHT(Arg2,8)

InternalStartDate = ICONV(StartDate,"D-YMD[4,2,2]")
InternalStartTime = ICONV(StartTime,"MTS")
InternalFinishDate = ICONV(FinishDate,"D-YMD[4,2,2]")
InternalFinishTime = ICONV(FinishTime,"MTS")

NetDays = InternalFinishDate - InternalStartDate

NetTime = 0
Begin Case
Case InternalFinishTime >= InternalStartTime
NetTime = InternalFinishTime - InternalStartTime
Case InternalFinishTime < InternalStartTime
NetTime = ((24 * 60 * 60) - InternalStartTime) + InternalFinishTime
NetDays -= 1
End Case

* Number of days difference * 24 hours * 60 minutes * 60 seconds + number of seconds elapsed

TotalElapsedSeconds = (NetDays * 24 * 60 * 60) + NetTime

If TotalElapsedSeconds < 0 or NetDays < 0 Then TotalElapsedSeconds = 0

Ans = TotalElapsedSeconds

Since I want the answer in minutes, can I do Ans = TotalElapsedSeconds/60??

Thanks !!

[ArndW]

Hmmm, your routine seems to do what the suggested code that I wrote does in more steps, plus it will only work if the elapsed time is never more than 24 hours - because you aren't checking the date but just the time values.

You can divide the seconds by 60 to get minutes and also do an INT() to truncate the values.

I though you wanted to do something in a transform; there are excellent routines out there that do what you wish AND include error checking. You can use the search facility to find them.

[its_me48]

Arnd,

Sorry, your message was not posted before I replied to Ken's answer.
I am sure both work fine but your solution helps me in this case since I want to put the code in the transformer.
I will try to implement and let you know.

Again, thanks for the ultra-quick reply; Ken & Arnd.

Sincerely,
-Sam

[its_me48]

Arnd,

Followed through your steps (code):
Converted date & time using Stage variables
Added the derivation logic to the duration column; the only difference, I had to divide (EndTime-StartTime/60) to give me the result in minutes.

Thanks for all your help...ditto Ken

Have a great weekend!!

-Sam