Home » SQL & PL/SQL » SQL & PL/SQL » Find out date range for last 4 and 52 weeks (Oracle 9i, Unix)
Find out date range for last 4 and 52 weeks [message #442418] Sun, 07 February 2010 23:00 Go to next message
sghoshal
Messages: 5
Registered: April 2007
Location: Kolkata
Junior Member
Hi,

I have a column in db DELIVERY_PERFORMANCE_YYWW (CHAR(6)). I have to select all records from the table where
DELIVERY_PERFORMANCE_YYWW between current date and current date - 4 weeks.

Can anyone please suggest the best way to do this.

Thanks.
Re: Find out date range for last 4 and 52 weeks [message #442426 is a reply to message #442418] Sun, 07 February 2010 23:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone please suggest the best way to do this.

use oracle's date arithmetic & your challenge is to convert to DATE datatype
Re: Find out date range for last 4 and 52 weeks [message #442435 is a reply to message #442418] Mon, 08 February 2010 01:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sghoshal wrote on Mon, 08 February 2010 06:00
Hi,

I have a column in db DELIVERY_PERFORMANCE_YYWW (CHAR(6)).


Wow.. 3 possible flaws in 1 go.
1) Dates are stored in date-fields
2) Use of CHAR for things other than non-nullable CHAR(1) fields is something that should at least be thought over twice
3) a column name implying a 4 digit code stored in 6 positions.

For your question, Blackswan already provided the best solution.
Re: Find out date range for last 4 and 52 weeks [message #442441 is a reply to message #442418] Mon, 08 February 2010 01:15 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
4 weeks is 28 days.

Database SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Section Datetime/Interval Arithmetic

Regards
Michel
Previous Topic: Procedure to do incremental update
Next Topic: ora:01476 divisor is equal to zero
Goto Forum:
  


Current Time: Fri Dec 09 13:49:57 CST 2016

Total time taken to generate the page: 0.11972 seconds