WHERE 5 business days

Question

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?

The following my help: http://stackoverflow.com/questions/14898357/calculate-business-days-in-oracle-sqlno-functions-or-procedure — Seymour, Dec 10 '13 at 18:46

score 1 · Answer 1 · answered Dec 10 '13 at 18:44

1

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

answered Dec 10 '13 at 18:44

Jorge Campos

22,647
7
56
87

Is there a command/function that returns 'MON', 0, 'TUE', 1, 'WED', 2, 'THU', 3...etc... that I can test SYSDATE to? – Zornjac Dec 10 '13 at 18:50
to_char(sysdate,'D') it will return the number of the day on the week but it depends on the configuration of your database starting day of the week. See this http://psoug.org/reference/date_func.html there is a table for every format you can use. – Jorge Campos Dec 10 '13 at 18:55
It actually depends on the NLS-Configuration of your **Session**, not of the database. In order to be sure that you get correct day you have to use function like this `TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = american')` – Wernfried Domscheit Dec 10 '13 at 19:23
1

You can have default NLS settings that are DB-wide. – jim mcnamara Dec 10 '13 at 19:25
@jimmcnamara That is what I mean. Wernfried you are also right, but if there is no session configuration it will be overrided by the DB configs. – Jorge Campos Dec 10 '13 at 22:25

score 0 · Answer 2 · answered Dec 10 '13 at 19:21

@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.

WHERE 5 business days

2 Answers2