specific function to add months in a date

Amar_nath · Post by **Amar_nath** » Wed May 17, 2006 5:13 am

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,

ashwin141 · Post by **ashwin141** » Wed May 17, 2006 5:24 am

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

Amar_nath · Post by **Amar_nath** » Wed May 17, 2006 5:41 am

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.

jgibby · Post by **jgibby** » Wed May 17, 2006 7:03 am

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

janhess · Post by **janhess** » Wed May 17, 2006 8:33 am

Looks like 25 to me.

jgibby · Post by **jgibby** » Wed May 17, 2006 9:37 am

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.

janhess · Post by **janhess** » Wed May 17, 2006 9:45 am

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

jgibby · Post by **jgibby** » Wed May 17, 2006 9:47 am

Then I am missing something.

My apologies!

DSguru2B · Post by **DSguru2B** » Wed May 17, 2006 9:49 am

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,

janhess · Post by **janhess** » Wed May 17, 2006 9:54 am

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.

jgibby · Post by **jgibby** » Wed May 17, 2006 10:07 am

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.

DSguru2B · Post by **DSguru2B** » Wed May 17, 2006 10:07 am

I apologize. I didnt pay attention that this is a TX forum.

janhess · Post by **janhess** » Wed May 17, 2006 10:37 am

I think if you add months you would expect the day to stay the same. ie only the month and year would change.

jgibby · Post by **jgibby** » Wed May 17, 2006 11:31 am

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

janhess · Post by **janhess** » Thu May 18, 2006 1:55 am

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