Home » SQL & PL/SQL » SQL & PL/SQL » Calculate 1 week before given date (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
Calculate 1 week before given date [message #336609] Mon, 28 July 2008 06:34 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi guys,

Given any date, i need to compute that date 1 week or 7 days prior to it, i wrote this small query given below taking sysdate, is there any other smaller way to achieve this given any date

select to_date(

to_number(to_char(sysdate,'DD')-7)|| '-' ||
to_char(sysdate,'MON')|| '-' || 
to_char(sysdate,'YYYY'),'DD-MON-YYYY') 

as prevWeek from dual;

              PREVWEEK
----------------------
             7/21/2008 





[Updated on: Mon, 28 July 2008 06:36]

Report message to a moderator

Re: Calculate 1 week before given date [message #336611 is a reply to message #336609] Mon, 28 July 2008 06:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You have over complicated it. Simply take the date and subtract 7.
Re: Calculate 1 week before given date [message #336612 is a reply to message #336611] Mon, 28 July 2008 06:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pablolee wrote on Mon, 28 July 2008 13:38
You have over complicated it. Simply take the date and subtract 7.

Not only overcomplicated, but done it wrong as well. What if sysdate would be 03-JAN-2009 ?
Re: Calculate 1 week before given date [message #336615 is a reply to message #336612] Mon, 28 July 2008 06:53 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
To be honest, I didn't look any further than the fact that the OP hadn't performed simple arithmetic.
Re: Calculate 1 week before given date [message #336617 is a reply to message #336615] Mon, 28 July 2008 06:57 Go to previous message
NewLife
Messages: 170
Registered: April 2008
Senior Member
U GUYS rock, thanks a ton Smile
Previous Topic: partition
Next Topic: Filtering Column
Goto Forum:
  


Current Time: Sat Dec 03 14:03:34 CST 2016

Total time taken to generate the page: 0.07543 seconds