specific function to add months in a date
specific function to add months in a date
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,
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
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
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
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
Also in your example, the difference in months between 12/4/2001 and 12/5/2003 is 24 months, not 25 months.
John
"Artificial intelligience is no match for natural stupidity."
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
By my calculations, adding 25 months to 12/2001 would come to 01/2004.
"Artificial intelligience is no match for natural stupidity."
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,
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,
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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.
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.
As long as the day of the month is 28 or less you'll have no problems using this formula:
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.
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}"
)
[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.
Last edited by jgibby on Fri May 19, 2006 3:19 pm, edited 1 time in total.
"Artificial intelligience is no match for natural stupidity."
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.
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.'