How to get diff between two dates in routine

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
ravij
Premium Member
Premium Member
Posts: 170
Joined: Mon Oct 10, 2005 7:04 am
Location: India

How to get diff between two dates in routine

Post by ravij »

Hi,
Y=Field(Oconv(D1, "D-YMD[4,2,2]"),'-',1)-Field(Oconv(D2, "D-YMD[4,2,2]"),'-',1)
Mon=Field(Oconv(D1, "D-YMD[4,2,2]"),'-',2)-Field(Oconv(D2, "D-YMD[4,2,2]"),'-',2)
If Substrings(Mon,1,1)='-' Then
M=12-Field(Mon,'-',2)
End
If Substrings(Mon,1,1)<>'-' Then
M=Mon
End
If M=0 Then
Ans=Y
End
If M>0 Then
Ans= Y:'.':M
End
In the above routine when i calculate the diff i am getting one year extra than the actual year. Please give the correct solution.
Any answer can be appreciated.
thanks in advance
Ravi
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Way too much work. :wink: Convert the two dates into Internal format and subtract them - that will give you the number of days between the two dates. You can then adjust from there if you need the diff in some other 'unit'.
-craig

"You can never have too many knives" -- Logan Nine Fingers
parag.s.27
Participant
Posts: 221
Joined: Fri Feb 17, 2006 3:38 am
Location: India
Contact:

Re: How to get diff between two dates in routine

Post by parag.s.27 »

ravij wrote:Hi,
Y=Field(Oconv(D1, "D-YMD[4,2,2]"),'-',1)-Field(Oconv(D2, "D-YMD[4,2,2]"),'-',1)
Mon=Field(Oconv(D1, "D-YMD[4,2,2]"),'-',2)-Field(Oconv(D2, "D-YMD[4,2,2]"),'-',2)
If Substrings(Mon,1,1)='-' Then
M=12-Field(Mon,'-',2)
End
If Substrings(Mon,1,1)<>'-' Then
M=Mon
End
If M=0 Then
Ans=Y
End
If M>0 Then
Ans= Y:'.':M
End
In the above routine when i calculate the diff i am getting one year extra than the actual year. Please give the correct solution.
Any answer can be appreciated.
thanks in advance
Hi,

You can simply use the DateGenericDateDiff transform function.
just go to --DSTransforms category>SDK>Date>Generic
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Craig has already pointed out that your program is doing a lot more than it needs to.

Since you do an OCONV() on D1 and D2 to get displayable dates, the assumption is that both D1 and D2 are already in internal format.

ABS(D2-D1) will give you the difference in days between the two dates, and if your logic is allowed to do so you can do a calculation of months difference by diving that amount by 30.4 (which is 365/12). This doesn't take into account leap years and exact month endings but is easy to do.

If your answer has to be correct in terms of real months (i.e. 2005-11-31 and 2005-12-01 need to return a value of "0.1") then you could stay with your original approach, but compress it to:

Code: Select all

Y1 = OCONV(D1,'DY')
Y2 = OCONV(D2,'DY')
M1 = OCONV(D1,'DM')
M2 = OCONV(D2,'DM')
Ans = Y2-Y1  ;** Number of years
IF Ans = 0 THEN Ans = Ans:'.':M2-M1
           ELSE Ans = Ans:'.':(12-M1)+M2
Note that the number of months can be greater than 12; I don't know if you want to add a year to your answer if that is the case...
ravij
Premium Member
Premium Member
Posts: 170
Joined: Mon Oct 10, 2005 7:04 am
Location: India

How to get diff between two dates in routine

Post by ravij »

Hi Chulett,

thanks, and from the days I want to calculate No. of years and No. of months. Ex: D1 is 2006-01-23 and D2 is 2004-10-15 then the result should be 1.3 yrs.

How should I proceed for that? Any idea please?

ArndW I am not able to read your message fully. How to get the full message?

thanks in advance.
Ravi
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Re: How to get diff between two dates in routine

Post by kumar_s »

ravij wrote:Hi Chulett,

thanks, and from the days I want to calculate No. of years and No. of months. Ex: D1 is 2006-01-23 and D2 is 2004-10-15 then the result should be 1.3 yrs.

How should I proceed for that? Any idea please?

ArndW I am not able to read your message fully. How to get the full message?

thanks in advance.
Arnds post are considired as premium content. You need to be a chartered member to get those full post. But you can get more result if you give a try in search.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Sunshine2323
Charter Member
Charter Member
Posts: 130
Joined: Mon Sep 06, 2004 3:05 am
Location: Dubai,UAE

Post by Sunshine2323 »

Hi,

Try this,
Date1=Iconv(InputDate1,"D-YMD[4,2,2]")
Date2=Iconv(InputDate2,"D-YMD[4,2,2]")
Year1 = OCONV(Date1,'DY')
Year2 = OCONV(Date2,'DY')
YearDifference=Year2-Year1
Month1 = OCONV(Date1,'DM')
Month2 = OCONV(Date2,'DM')
MonthDifference=Month2-Month1

IF MonthDifference<0 and YearDifference<>0 then Ans=(YearDifference+MonthDifference):'.':(12+MonthDifference) else Ans = YearDifference:'.':Abs(MonthDifference)

I tried with the following sample data and got these outputs:
2004-5-2 2006-6-8 2.1
2004-5-2 2008-4-2 3.11
2003-2-4 2003-4-4 0.2
2003-2-4 2003-5-4 0.3

Hope this helps :)
Warm Regards,
Amruta Bandekar

<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
ravij
Premium Member
Premium Member
Posts: 170
Joined: Mon Oct 10, 2005 7:04 am
Location: India

How to get diff between two dates in routine

Post by ravij »

Hi Sunshine,

Thanks its working fine.

Thank you all for your immediate response.
Ravi
ravij
Premium Member
Premium Member
Posts: 170
Joined: Mon Oct 10, 2005 7:04 am
Location: India

How to get diff between two dates in routine

Post by ravij »

Hi parag.s.27,
You can simply use the DateGenericDateDiff transform function.
Can u just explain how to do with this function please? As i read in the Server guide this function gives no. of days between two dates. If u get this again we need to take care of Leap Year also know(please correct me if I am wrong). How to use this function?

thanks in advance
Ravi
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

When dates are handled in internal format, leap years are handled automatically. So too is the strange year in the 16th century when the Pope added 11 days to a year to get the calendar re-synchronized with the seasons. Don't worry. Be happy.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ravij
Premium Member
Premium Member
Posts: 170
Joined: Mon Oct 10, 2005 7:04 am
Location: India

Post by ravij »

Hi Ray,

I am sorry , that i am not able to read your message fully. What to do to get the full message. Someone inthis post mentioned that to read that message we should get membership.
Could you give some details about it please?

thanks in advance.
Ravi
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Ravi, see the most recent Newsletter for details on membership. That being said, are we not still in the 'Trial period' or has that expired? :? It's impossible for me to tell as we don't see that button - does it still say 'Continue trial' under it? I should know this, but...
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

The trial period was February. I undestand that we're "go" for premium content now.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply