1

how to add a number of days to Date field and skipping the calculation of weekends.

ex: if  Date  = 7/7/2021  then  Due Date = 07/21/2021`

Date = uxsubmtlDateRcvdDOP,
Number of days = 10, 
Due Date = uxsubmtlDateRcvdDOP + 10`

I have

Select uxsubmtlDateRcvdDOP + 10 as duedate

but that calculates the weekends as well.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
Zee
  • 15
  • 1
  • 7
  • 1
    Does this answer your question? [Calculate difference between 2 dates in SQL, excluding weekend days](https://stackoverflow.com/questions/8331951/calculate-difference-between-2-dates-in-sql-excluding-weekend-days) – Matthias Jul 19 '21 at 21:22
  • 1
    @matthias That question is full of answers about SQL Server (T-SQL) though it has no such tag. But I'll find the same about Oracle because it was asked few weeks ago – astentx Jul 19 '21 at 21:29
  • @Matthias That question is also performing the calculation in the wrong direction as it takes two dates as input and calculates the number of weekdays between them as the output; the OP wants to take one date and a number of weekdays as input and calculate a date as the output. – MT0 Jul 19 '21 at 22:59
  • Are your working days Monday through Friday? (That is not the case in many countries - you should always state exactly what you mean by "work week".) Also, what if the input date is a Saturday or Sunday? I assume in such a case, adding 1 work day means returning the following Monday, but please confirm either way. –  Jul 20 '21 at 00:55

3 Answers3

1

You can use:

SELECT date_value,
       added_weekdays,
       weekday_date_value
       + MOD(added_weekdays, 5)        -- Number of days of part week
       + FLOOR(added_weekdays / 5) * 7 -- Number of full weeks
       + CASE TRUNC( weekday_date_value + MOD(added_weekdays, 5) )
              - TRUNC( weekday_date_value + MOD(added_weekdays, 5), 'IW' )
         WHEN 5 THEN 2
         WHEN 6 THEN 1
         ELSE 0 END                    -- Shift value if it falls on a weekend.
         AS final_value
FROM   (
  SELECT t.*,
         date_value
         + CASE TRUNC(date_value) - TRUNC(date_value, 'IW')
           WHEN 5 THEN 2
           WHEN 6 THEN 1
           ELSE 0 END AS weekday_date_value -- Shift the start date if it is on
                                            -- a weekend. 
  FROM   table_name t
)

Which, for the sample data:

CREATE TABLE table_name ( date_value, added_weekdays ) AS
SELECT DATE '2021-07-07', 10 FROM DUAL UNION ALL
SELECT DATE '2021-07-07', 11 FROM DUAL UNION ALL
SELECT DATE '2021-07-07', 12 FROM DUAL UNION ALL
SELECT DATE '2021-07-07', 13 FROM DUAL UNION ALL
SELECT DATE '2021-07-10',  0 FROM DUAL UNION ALL
SELECT DATE '2021-07-10',  1 FROM DUAL;

Outputs (with the date format of YYYY-MM-DD (DY)):

DATE_VALUE ADDED_WEEKDAYS FINAL_VALUE
2021-07-07 (WED) 10 2021-07-21 (WED)
2021-07-07 (WED) 11 2021-07-22 (THU)
2021-07-07 (WED) 12 2021-07-23 (FRI)
2021-07-07 (WED) 13 2021-07-26 (MON)
2021-07-10 (SAT) 0 2021-07-12 (MON)
2021-07-10 (SAT) 1 2021-07-13 (TUE)

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
1

This can be done in Oracle with a small PL/SQL function that uses TRUNC(<date>, 'IW') to find the Monday of the week containing your date.

create or replace function plus_weekdays(d0 date, weekdays number) return date
is
  d0_Monday            date;    /* Monday <= d0 */
  weekdays_from_Monday number;  /* # of weekdays to increment from d0_Monday */
  nweeks               int;     /* # of weeks to increment from d0_Monday */
  ndays                number;  /* # of days to increment after nweeks */
begin
  d0_Monday := trunc(d0, 'iw');

  if d0 - d0_Monday >= 5 then
    -- This case means the given date is a Saturday or Sunday.
    -- We round the given date up to the next Monday and decerement the 
    -- number of weekdays that we are adding. For example:
    --   Saturday - 1 = Thursday (computed as Monday - 2)
    --   Saturday + 0 = Friday   (computed as Monday - 1)
    --   Saturday + 1 = Monday   (computed as Monday + 0)
    --   Saturday + 2 = Tuesday  (computed as Monday + 1)

    d0_Monday := d0_Monday + 7;
    weekdays_from_Monday := weekdays - 1;
  else
    weekdays_from_Monday := weekdays + (d0 - d0_Monday);
  end if;

  nweeks := floor(weekdays_from_Monday / 5);
  ndays  := (weekdays_from_Monday - nweeks * 5);

  return d0_Monday + nweeks * 7 + ndays;
end;
/
show errors;

SQL> select plus_weekdays(DATE '2020-07-07', 10) from dual;

PLUS_WEEK                                                                       
---------                                                                       
21-JUL-20
George Eadon
  • 913
  • 5
  • 9
  • Please note that `remainder` is an Oracle keyword, so it would be better not to use it as a variable name in your code. Also, the OP should clarify the desired behavior if the input date is a Saturday/Sunday (presumably adding 1 work day to that should be the following Monday, in which case you would need a small modification of the code to account for that). Other than that, though, this is probably the simplest approach. +1 –  Jul 20 '21 at 00:54
  • Fair points. I've updated the PL/SQL code based on your comments. Thanks for taking a look. – George Eadon Jul 20 '21 at 04:03
-1
Dateadd(mydate,,trunc(mydays/5)*7 + mod(mydays,5) + 2*trunc((weekday(mydate)+mod(mydays,5))/7))

The last term checks to see if you crossed a final weekend boundary.

This is a little weird if your starting date is Saturday or Sunday. Friday + 1 day = Monday, Saturday + 1 day = Tuesday, but Sunday + 1 day is Monday.

Chris Maurer
  • 2,339
  • 1
  • 9
  • 8
  • thanks for your answer but Dateadd does not work in Oracle Sql developer and thats what threw me off. – Zee Jul 21 '21 at 13:21