Oracle 10g - Subtract 1 from 'IW' week number

I suppose , you're just mixing up TRUNC and TO_CHAR ;-)
Exchange it, and it'll work

and TO_CHAR(O.ORD_CONF_DATE, 'IW') = TO_CHAR(SYSDATE, 'IW')-1

Select allOpen in new window

see: https://stackoverflow.com/questions/32603365/what-exactly-does-truncdate-iw

At first glance it appears to work, but this method does not consider the year. It is returning records for the correct week number, but for the last 12 years of data

?!? Let's take a step back: your code did not work, because you wanted to compare ISO weeks (reaching from 1 to 52). The TRUNC does NOT the trick, so you'd have to change it to TO_CHAR as I suggested.
So, what exactly are your requirements then??

I simply need to select all records from last week.
I am attempting to use the Week number instead of subtracting days, so the query can be run at any point during the following week.
Sorry for the confusion

Ok, then why don't you just do:

and O.ORD_CONF_DATE >= trunc(sysdate - 7)

Select allOpen in new window

or

and O.ORD_CONF_DATE >= trunc(sysdate) - interval '7' day

Select allOpen in new window

As mentioned above, this would be ideal in a scheduled query that runs at an exact time
I am trying to give the flexibility that the report can be run days later looking back at the prior week

Then you need to have a reference date, like a variable (date) in exchange to SYSDATE...

To get the previous week NOT the previous 7 days, take a look at this previous question:
https://www.experts-exchange.com/questions/27632827/ORACLE-previous-week-date-function.html

IW likely won't work.  What do you get if I run the report on Jan 5th?

I think what I will do is use the TO_CHAR you provided, then just add another clause further limiting the records returned to 1yr to eliminate prior years

I'm afraid neither you nor we do fully understand the specs/requirements :-(

Use date math to look for date values.  You shouldn't convert dates to strings then strings to numbers to do 'math' on them just to pull back date values from a table.

Hi JD,

I'm thinking that you're making this harder than it needs to be.

  WHERE trunc (O.ORD_CONF_DATE) BETWEEN trunc (sysdate) - weekday (sysdate) -7 AND trunc (sysdate) - weekday (sysdate) -1

That will select rows with dates from last week, Sunday through Saturday.  

Kent

Got to say, I'm a little confused as well.  Is this what you need?

O.ORD_CONF_DATE >= TRUNC(SYSDATE,'IW')-7 and O.ORD_CONF_DATE < TRUNC(SYSDATE,'IW')

That assumes that TRUNC(SYSDATE,'IW') gives you what you are looking for.

Personally, I would use BETWEEN, but that syntax is frowned upon here.  If you like BETWEEN, then the syntax would be:

O.ORD_CONF_DATE BETWEEN TRUNC(SYSDATE,'IW')-7 and TRUNC(SYSDATE,'IW') - (1/86400)

Sorry. I have caused a lot of confusion.
let me explain the practical use so you might better understand the need

Sales orders are entered in the system. I need to report last weeks sales. However, many sales records from last week still require completion in the days following. It might be Tues, Wed or Thurs before last weeks records are completed and can be accurately reported.
Regardless if the report is run on Tues, Wed or Thurs I always want it to look back at the previous calendar week.

This is like fishing in the murky waters!! The OP has to clarify what exactly he needs, period!

Then, the start of the last week would be: trunc(sysdate - 7, 'IW')
And the other boundary would be : trunc(sysdate, 'IW') - 1

Can you show me how trunc(sysdate - 7, 'IW') generates a different value than trunc(sysdate, 'IW') - 7?

If the column has a time component, using trunc(sysdate, 'IW') - 1 would likely miss data for the last day.

Can you show me how trunc(sysdate - 7, 'IW') generates a different value than trunc(sysdate, 'IW') - 7?

No, cause it's the same, but that was NOT the purpose ;-)

If the column has a time component, using trunc(sysdate, 'IW') - 1 would likely miss data for the last day.

As always: what are the OP's reqs and specs ;-)

Specs as posted say last week.  That would include the entire day for the last day of the week.  At least the specs don't say leave out the last day.  You are confusing a difference in specs with a difference in data.

So, the 3 dates that I posted, generate these:

SELECT Trunc(SYSDATE, 'IW') - 7             start_dt, 
       Trunc(SYSDATE, 'IW')                 end_dt_gt_lt, 
       Trunc(SYSDATE, 'IW') - ( 1 / 86400 ) end_dt_between 
FROM   dual; 

START_DT               END_DT_GT_LT            END_DT_BETWEEN
---------------------  ----------------------  ----------------------
25-mar-2019 00:00:00   01-apr-2019 00:00:00    31-mar-2019 23:59:59

Select allOpen in new window

Those appear to line up to me.People here don't typically like the syntax that I use, so I'll just leave it at that.