Oracle - Filter results between modified working week

Question

Using Oracle, I am currently filtering data using AND schedstart BETWEEN (TRUNC(SYSDATE - (TO_CHAR(SYSDATE,'D')+6))) AND (TRUNC(SYSDATE - (TO_CHAR(SYSDATE,'D')-1)))) which isn't what I want.

My working week starts on a Wednesday and finishes on a Tuesday so I want my results to be filtered on my current working week. Any help would be very much appreciated.

Answer 1

To find the beginning of the next week you would use Next_Day():

Next_Day(Trunc(SysDate),'WED')

Therefore to find the beginning of "this" week, use:

Next_Day(Trunc(SysDate),'WED') - 7

So to limit schedstart to the current working week (assuming schedstart has no time component):

schedstart between Next_Day(Trunc(SysDate),'WED') - 7
               and Next_Day(Trunc(SysDate),'WED') - 1