Page 1 of 2
specific function to add months in a date
Posted: Wed May 17, 2006 5:13 am
by Amar_nath
hello all,
I am very new to DS
Is there any Inbuild function in DS to add number of months to a date so it will give the resulting date.
Thanx in advance,
specific function to add months in a date
Posted: Wed May 17, 2006 5:24 am
by ashwin141
Hi Amar
Try playing with MonthFromDate and MonthDayFromDate and DateFromDaysSince. I guess it will help you. Otherwise please explain more so that we can help you.
Regards
Ashwin
Posted: Wed May 17, 2006 5:41 am
by Amar_nath
Hi Ashwin,
I think the problem is not clear, let me clear by am ex.
Example : date is 12/4/2001
if i want add 25 months in it
it should give resulting date as : 12/5/2003
So is there any inbuild function for that ?
thanx.
Posted: Wed May 17, 2006 7:03 am
by jgibby
There is no built in function to add months that I know of in Datastage TX. I'm not sure what Ashwin is referring to. There is an ADDDAYS function but that is about it.
Also in your example, the difference in months between 12/4/2001 and 12/5/2003 is 24 months, not 25 months.
John
Posted: Wed May 17, 2006 8:33 am
by janhess
Looks like 25 to me.
Posted: Wed May 17, 2006 9:37 am
by jgibby
janhess wrote:Looks like 25 to me.
Code: Select all
12/2001
+1 1 Month
01/2002
+1 2 Months
02/2002
+1 3 Months
03/2002
+1 4 Months
04/2002
+1 5 Months
05/2002
+1 6 Months
06/2002
+1 7 Months
07/2002
+1 8 Months
08/2002
+1 9 Months
09/2002
+1 10 Months
10/2002
+1 11 Months
11/2002
+1 12 Months
12/2002
+1 13 Months
01/2003
+1 14 Months
02/2003
+1 15 Months
03/2003
+1 16 Months
04/2003
+1 17 Months
05/2003
+1 18 Months
06/2003
+1 19 Months
07/2003
+1 20 Months
08/2003
+1 21 Months
09/2003
+1 22 Months
10/2003
+1 23 Months
11/2003
+1 24 Months
12/2003
What am I missing
By my calculations, adding 25 months to 12/2001 would come to 01/2004.
Posted: Wed May 17, 2006 9:45 am
by janhess
Yes but you're using American Date Format.
The example used UK (and probably the rest of the world) date format which is DD/MM/CCYY
Posted: Wed May 17, 2006 9:47 am
by jgibby
Then I am missing something.
My apologies!
Posted: Wed May 17, 2006 9:49 am
by DSguru2B
Both of you guys are correct.
Depending upon how you look at it. Both of you guys are looking at different Date formats and coming up with the result.
As for the Original poster. You need to develop a routine to add the months. If you can translate the month into days then that becomes even more easier as you can just Iconv your date, add the days and Oconv it to your desired output.
Regards,
Posted: Wed May 17, 2006 9:54 am
by janhess
What's Iconv and Oconv? Doesn't sound like DSTX function.
You can use the ADDDAYS if you know how many days or set up an algorithm to work it out. Similarly you could convert the date to a number using DATETONUMBER, add the number of days and convert it back to a date using NUMBERTODATE. None of them are easy.
Posted: Wed May 17, 2006 10:07 am
by jgibby
As long as the day of the month is 28 or less you'll have no problems using this formula:
Code: Select all
=TODATETIME(
FROMDATETIME(DateValue,"{DD}")
+ "/"
NUMBERTOTEXT(
TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}"))
+ (
MonthsToAdd - INT(MonthsToAdd / 12)
)
)
+ "/"
NUMBERTOTEXT(
TEXTTONUMBER(FROMDATETIME(DateValue,"{CCYY}"))
+ INT(MonthsToAdd / 12)
)
,"{DD/MM/CCYY}"
)
But once the day of the month is above 28, you could have a major headache doing it. Does that look right Jan?
[EDIT]
This code will not work. I hacked it up quickly in a text editor and didn't test it. The actual formula is quite complex, but I do have a working one later in the thread.
Posted: Wed May 17, 2006 10:07 am
by DSguru2B
I apologize. I didnt pay attention that this is a TX forum.
Posted: Wed May 17, 2006 10:37 am
by janhess
I think if you add months you would expect the day to stay the same. ie only the month and year would change.
Posted: Wed May 17, 2006 11:31 am
by jgibby
Yeah, but what is the rule if I add one month to January 31, 2005?
February 31, 2005 isn't valid. Is the answer February 28, 2005?
John
Posted: Thu May 18, 2006 1:55 am
by janhess
In that case I don't think it would be sensible to add months. I suppose it depends on what Amar_nath wants to do.
Perhaps if you were working on the last day of the month, you could use ADDDAYS to add 1 day and get the start of the next month, add the months and use ADDDAYS to add -1 day to get the last day of the month before. However, this doesn't handle 29 and 30 if they aren't the last days.
This is what the Oracle function ADD_MONTHS does.
ADD_MONTHS(d,n)
'ADD_MONTHS returns the date d plus n months. The argument n can be any integer.
If d is the last day of the month or if the resulting month has fewer days than the
day component of d, then the result is the last day of the resulting month.
Otherwise, the result has the same day component as d.'