CODEWITHSUNDEEP
sqloracle-databasedate
Zornjac
Zornjac

Reputation: 261

WHERE 5 business days

I'm trying to get the next 5 business days, using sysdate in the WHERE (ie. 

where trunc(teststart) between trunc(sysdate) and trunc(sysdate+4)),

but if the range includes Friday, it needs to count weekend days, Saturday & Sunday.

What is the command that will tell you the day of the week when looking at the sysdate or sysdate+3 (or any number)? How would you accomplish something like this?

Upvotes: 0

Views: 258

Answers (2)

jim mcnamara
jim mcnamara

Reputation: 16389

@JorgeCampos point is well taken about day 1. You indicate with MON 0 that Monday is the start of the week, NLS settings on the DB and your locale determine this.

select to_char(sysdate, 'D'), to_char(sysdate,'DAY') from dual;

gives the result you asked for. We have a table that gets the next year populated in December for the next year. Includes all holidays, etc. 

 JCAL_JOB_DATE                          NOT NULL DATE
 JCAL_USER_ID                                    VARCHAR2(30)
 JCAL_ACTIVITY_DATE                              DATE
 JCAL_BUSINESS_DAY                               VARCHAR2(2)
 JCAL_HOLIDAY                                    VARCHAR2(2)
 JCAL_WEEKEND                                    VARCHAR2(2)

This is used to forecast jobs in the future, and some recurring jobs that do not want to run on holidays or weekends for example. 

select jcal_job_date from jcal where 
    JCAL_JOB_DATE > sysdate
and JCAL_BUSINESS_DAY='Y'
and rownum <6;

This is one not-so-good way to get the fifth business day. Fetch You can use a lot of other functions to streamline this.

This kind of table is VERY useful to determine the same information (work, holiday, weekend) in the past.

Where I am we have holidays that fall on the last Thursday of November and extra holidays are added in there. Programming for that is hard when compared to a simple lookup that works great.

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23381

You can't do this calculation because there is no way that oracle know what exactly is a business day. This is because every region has your own hollidays. To do this you will have to create a table calendar and put all days on it marking which ones is business day. Then you can just join your table with this calendar table using a between clause

Upvotes: 1

Related Questions