Subtract in weeks from date

[marisam]

Help!!
What is the correct syntax to subtract weeks from a date.

SELECT TABLE.DATE ADD_MONTHS(T00149.DATECH, 1) "Deadline"

from TABLE.DATE
Thanks

 

[Dagon]

If you just mean subtracting n periods of 7 days, you can just do it with:

select sysdate - (7* n ) from dual

 

[marisam]

Thanks for the response.

I need to display a weekly reminder. We start with 4 weeks before the 3 month deadline and continue to do so at the three week, 2nd week etc.
As of March 1st 2007

Final Reminder
Deadline Date Countdown
Date

01-Jun-2007 01-Mar-2007 4 weeks before deadline

As of March 08th 2007

Final Reminder
Deadline Date Countdown
Date

01-Jun-2007 08-Mar-2007 3 weeks before deadline


SELECT TABLE.DATE ADD_MONTHS(T00149.DATECH, 1) "Deadline"

from TABLE.DATE

 

[Dagon]

How do you work out that '01-Mar-2007' is 4 weeks before '01-jun-07' ? That is more than 12 weeks.

 

[marisam]

You are right! Apparently I'm still sleeping. Sorry
Final Reminder
Deadline Date Countdown
Date

01-Jun-2007 01-May-2007 4 weeks before deadline

As of March 08th 2007

Final Reminder
Deadline Date Countdown
Date

01-Jun-2007 08-May-2007 3 weeks before deadline


SELECT TABLE.DATE ADD_MONTHS(T00149.DATECH, 1) "Deadline"

from TABLE.DATE

 

[Dagon]

I'm still not very clear what your problem is. Something like the following will give you the number of weeks and days between the two dates:

select floor((to_date('01-jun-2007', 'DD-MON-YYYY') - to_date('24-may-2007', 'DD-MON-YYYY'))/7) no_weeks,
          mod((to_date('01-jun-2007', 'DD-MON-YYYY') - to_date('24-may-2007', 'DD-MON-YYYY')),7) no_days from dual