DSXchange

Is there any date function in datastage server to find out the date of the specified months ...like for eg what will be date after 6 months or what was the date before 6 months as of todays date

Yes, this can be done using the ICONV() and OCONV() functions. First, use ICONV() to convert your date into then internal format, which is an integer number of days since Dec 31, 1967. Once you have this internal format you have various options, for instance adding or subtracting 182 days and then converting it back to a display date using OCONV(). Or you can use one of the ICONV() functions to get the day & month and can then just add 6 months yet keep the same day. It all depends upon what you define as "6 months".

SQL> select add_months(sysdate,6) from dual;

ADD_MONTH
---------
06-DEC-08

oconv(iconv('06/06/2008',"D/E")+182,"D/DMY[2,2,4]") ---gives output -->05/12/2008

Sachin - that is the exact point I made in my post. Now we need to know what the original poster really wishes to do.

Thanks Sachin and ArndW for your suggestion But what i need is how to implement the add_months in datastage server.

So i what to write a routine say addmonth(X, Arg1) where x is number of months and Arg1 is a date and it should return date x months after the date in argument 1 of the routine taking into account the leap year.

X can take any integer value say 5,123 ect

Lets assume your date is in the format YYYYMMDD and is passed into your routine as "InDate" and your second parameter is "AddMonths" which is a value between -12 and 12.

Assuming your month's day is still valid six months out.

I forgot to add those lines, thanks for reminding me, Craig.

TestDate = ICONV(Ans,'D4YMD')
Result = @STATUS
BEGIN CASE
CASE Result = 3
CALL DSLogWarn('Oh No, Mr. Bill! The computed date "':Ans:'" has been converted to "':OCONV(Ans,'D4/YMD'):'".,'MyProgram')
CASE Result = 1
CALL DSLogWarn('The computed date "':Ans:'" is invalid.','MyProgram')
END CASE

... or simply rewind the date until it is valid.

From what I recall, you could also use OConv to automatically 'adjust' a date that has overflowed a month, if that works for your requirements. For example, adding six months to 08/31/2007 gives you 2/31/2008. If turning that into 3/2/2008 is the answer rather than 'rewinding' back to 2/29, that can be done as well.

A fixable invalid date returns a status() of 2 but a valid date, which is why I skipped it in my case statement.