DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
ravij



Group memberships:
Premium Members

Joined: 10 Oct 2005
Posts: 170
Location: India
Points: 1594

Post Posted: Thu Mar 02, 2006 4:38 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Windows
Hi,

Quote:
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42581
Location: Denver, CO
Points: 219161

Post Posted: Thu Mar 02, 2006 4:55 am Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
parag.s.27
Participant



Joined: 17 Feb 2006
Posts: 221
Location: India
Points: 2246

Post Posted: Thu Mar 02, 2006 5:18 am Reply with quote    Back to top    

ravij wrote:
Hi,

Quote:
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
Rate this response:  
Not yet rated
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16305
Location: Germany
Points: 92459

Post Posted: Thu Mar 02, 2006 5:31 am Reply with quote    Back to top    

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 alre ...

_________________

Image
Rate this response:  
Not yet rated
ravij



Group memberships:
Premium Members

Joined: 10 Oct 2005
Posts: 170
Location: India
Points: 1594

Post Posted: Thu Mar 02, 2006 6:02 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
kumar_s

Premium Poster


since February 2006

Group memberships:
Premium Members, Heartland Usergroup

Joined: 16 Jun 2005
Posts: 5242

Points: 26357

Post Posted: Thu Mar 02, 2006 6:19 am Reply with quote    Back to top    

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'
Rate this response:  
Not yet rated
Sunshine2323


since February 2006

Group memberships:
Premium Members

Joined: 06 Sep 2004
Posts: 130
Location: Dubai,UAE
Points: 1466

Post Posted: Thu Mar 02, 2006 6:57 am Reply with quote    Back to top    

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 Smile

_________________
Warm Regards,
Amruta Bandekar

If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut.
--Albert Einstein
Rate this response:  
Not yet rated
ravij



Group memberships:
Premium Members

Joined: 10 Oct 2005
Posts: 170
Location: India
Points: 1594

Post Posted: Thu Mar 02, 2006 7:12 am Reply with quote    Back to top    

Hi Sunshine,

Thanks its working fine.

Thank you all for your immediate response.

_________________
Ravi
Rate this response:  
Not yet rated
ravij



Group memberships:
Premium Members

Joined: 10 Oct 2005
Posts: 170
Location: India
Points: 1594

Post Posted: Fri Mar 03, 2006 6:06 am Reply with quote    Back to top    

Hi parag.s.27,

Quote:
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
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54225
Location: Sydney, Australia
Points: 294087

Post Posted: Fri Mar 03, 2006 6:37 am Reply with quote    Back to top    

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-synchroniz ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
ravij



Group memberships:
Premium Members

Joined: 10 Oct 2005
Posts: 170
Location: India
Points: 1594

Post Posted: Fri Mar 03, 2006 6:52 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42581
Location: Denver, CO
Points: 219161

Post Posted: Fri Mar 03, 2006 8:13 am Reply with quote    Back to top    

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? Confused 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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54225
Location: Sydney, Australia
Points: 294087

Post Posted: Fri Mar 03, 2006 9:45 am Reply with quote    Back to top    

The trial period was February. I undestand that we're "go" for premium content now.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours