CODEWITHSUNDEEP
oracle-databasedatebetweento-charsysdate
user3175423
user3175423

Reputation: 1

Oracle - Filter results between modified working week

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.

Upvotes: 0

Views: 265

Answers (1)

David Aldridge
David Aldridge

Reputation: 52396

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

Upvotes: 0

Related Questions