Oracle - Filter results between modified working week

0
votes

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.

1
oracledatebetweento-charsysdate
Applying To_Char() to sysdate converts it to a character string. Probably what you intend is to remove the time component - do that with Trunc(sysdate) – David Aldridge

1 Answers

0
votes

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