Thread: Function in datastage to find date after 6 months

Is there any date function in datastage 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

Question asked by visitor Vikram Mohta

select add_months(sysdate,6),add_months(sysdate,-6) from dual
Try this

select dateadd(month,6,getdate())

u should try this
SQL>select to_date(sysdate)+3,to_date(sysdate)-3 from dual;

Originally Posted by aditi14

u should try this
SQL>select to_date(sysdate)+3,to_date(sysdate)-3 from dual;

you need to read the question carefully before replying to those.

You have LAST_DAY and FIRST_DAY functions to get the last day and first day of the month and the input passed to these functions is "a date".If you pass SYSDATE (17-JAN-2009), then the value returned by FIRST_DAY will be 01-JAN-2009 and by LAST_DAY it will be 31-JAN-2009.

For finding dates taht are 6 months before or after, you will need to write a user defined function. If you are not so strict about 30 or 31 days in a month, then 6 months - 6 * 30 = 180 days. You can either add 180 to the SYSDATE or subtract 180 from SYSDATE to get the value using a simple SELECT statement like this:

Code:

SELECT SYSDATE - 180 six_months_before, SYSDATE + 180 six_months_after FROM dual;