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
[b]Question asked by visitor Vikram Mohta[/b]
Printable View
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
[b]Question asked by visitor Vikram Mohta[/b]
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;
[QUOTE=aditi14;36538]u should try this
SQL>select to_date(sysdate)+3,to_date(sysdate)-3 from dual;[/QUOTE]
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;
[/code]