How do we get two days prior date from today excluding weekends In Oracle?

Question

0.00/5 (No votes)

See more:

Hi,

How can we get two days prior date from today's date in Oracle?

lets say, today's date is 8/13/2015 so the result should be 8/10/2015

But in the case of weekends,
lets say today's date is 8/10/2015 so the result should be 8/05/2015

Please advise.

Thanks.

Posted 13-Aug-15 0:43am

DPM20

Updated 13-Aug-15 1:15am

v2

Add a Solution

Comments

Maciej Los 13-Aug-15 7:03am

Using Oracle sql only?

DPM20 13-Aug-15 7:12am

Oracle.

I have updated the question.

Sorry for not including that.

Maciej Los 13-Aug-15 7:54am

Don't worry ;) I wanted to be sure for 100%. The question has been properly tagged.
Please, see my answer.

2 solutions

Solution 2

A little bit different approach which doesn't require a PL/SQL block.

You can use a recursive query to build a set of dates. In that query check the day of the week and based on that, include it in calculation or not. In other words only working days are counted.

Consider the following statement:

SQL

WITH dates (SingleDate, DayOfWeek, Include, DayCumulator) AS (
   SELECT TRUNC(SYSDATE - level + 1) AS SingleDate,
          TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D')) AS DayOfWeek,
          CASE TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D'))
             WHEN 1 THEN 0
             WHEN 7 THEN 0
             ELSE 1
          END AS Include,
          SUM(CASE TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D'))
             WHEN 1 THEN 0
             WHEN 7 THEN 0
             ELSE 1
          END ) OVER (ORDER BY TRUNC(SYSDATE - level + 1) DESC) AS DayCumulator
   FROM dual
   CONNECT BY Level <= 1000
)
SELECT SingleDate,
       DayOfWeek,
       Include,
       DayCumulator
FROM dates

It would return a result set like

SingleDate  DayOfWeek  Include   DayCumulator
----------  ---------  -------   ------------  
15.08.2015  6          1         1
14.08.2015  5          1         2
13.08.2015  4          1         3
12.08.2015  3          1         4
11.08.2015  2          1         5
10.08.2015  1          0         5
09.08.2015  7          0         5
08.08.2015  6          1         6
07.08.2015  5          1         7
06.08.2015  4          1         8
05.08.2015  3          1         9
04.08.2015  2          1         10
03.08.2015  1          0         10
02.08.2015  7          0         10
01.08.2015  6          1         11
...

So now to get the date with certain amount of days in between, just add a WHERE condition, like

SQL

WITH dates (SingleDate, DayOfWeek, Include, DayCumulator) AS (
   SELECT TRUNC(SYSDATE - level + 1) AS SingleDate,
          TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D')) AS DayOfWeek,
          CASE TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D'))
             WHEN 1 THEN 0
             WHEN 7 THEN 0
             ELSE 1
          END AS Include,
          SUM(CASE TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D'))
             WHEN 1 THEN 0
             WHEN 7 THEN 0
             ELSE 1
          END ) OVER (ORDER BY TRUNC(SYSDATE - level + 1) DESC) AS DayCumulator
   FROM dual
   CONNECT BY Level <= 1000
)
SELECT SingleDate,
       DayOfWeek,
       Include,
       DayCumulator
FROM dates
WHERE DayCumulator = 7;

The result would be

SingleDate  DayOfWeek  Include   DayCumulator
----------  ---------  -------   ------------  
07.08.2015  5          1         7

Note that NLS settings may affect which number is returned for each weekday.

Posted 15-Aug-15 5:49am

Wendelius

Comments

Maciej Los 15-Aug-15 12:12pm

5ed!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-08-13T01:52:00

It's quite simple. You need to get today's date[^], then loop[^] till counter is less then 3. In each step of loop increase counter if weekday function[^] returns 0 to 5. In each step of loop you have to fetch date to variable (defined out of loop) using DateAdd function[^] (every time use that variable and substract -1 day).

That's all! Try!

In pseudo code (i'm not familiar with Oracle):

SQL

DECLARE:
var @counter INT :=1
var @twoWorkingDaysBefore DATETIME := SYSDATE()
BEGIN
    WHILE counter<3
        @twoWorkingDaysBefore := DATEADD(@twoWorkingDaysBefore INTERVAL -1 DAY)
        IF(WEEKDAY(@twoWorkingDaysBefore)>=0 AND  WEEKDAY(@twoWorkingDaysBefore) <5) 
        BEGIN
            @counter := @counter +1
        END
    LOOP
END
-- @twoWorkingDaysBefore stores proper date ;)