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 !!
Subtracting date and converting into minutes
Moderators: chulett, rschirm, roy
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):
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:
86400 is the number of seconds in a day (60*60*24)
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')
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)
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 !!
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 !!
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.
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.
Got to work....
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
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