CODEWITHSUNDEEP
mysqlsqldateinterval
Luthando Ntsekwa
Luthando Ntsekwa

Reputation: 4218

MYSQL Add working days to date

I want to add 5 days to the provided date, but the calculation must skip weekends.

I already know how to add 5 days without skipping weekends:

SELECT DATE_ADD(`date_field`, INTERVAL 5 DAY) As FinalDate
FROM `table_name`;

Now I want the returned value to skip weekends.

Currently if date_field = 2016-07-22 the results will be 2016-07-27
But I want the results to be 2016-07-29

Upvotes: 4

Views: 12281

Answers (8)

PYC
PYC

Reputation: 21

I propose you a solution that I tested in MARIA DB based on trivial mathematics

it is based on this analysis : A week is 7 days, the saturday and sundays are recurrent every each 5 times ... so you just have to add +2 for each every 5 days

First of all I reused a function that I saw in another post to make sure that Monday has number 0 but not 3 (as dayofweek gives 0 for Sunday)

Then I simply create a function where I performed my computation .

In the code below it's very academic so I put many variables to show each steps one by one So do not be surprised, it's just so that you can catch all steps

here the code

DELIMITER $$
CREATE or replace FUNCTION PYC_OurOwnDayOfWeek (v_date DATETIME, v_whichfirstDay integer) return integer
AS
   _dayOfWeek integer;
    _offset integer;
BEGIN
  _offset:=8-v_whichfirstDay;
  _dayOfWeek:=(DAYOFWEEK(v_date) + _offset) mod 7;
  if (_dayOfWeek = 0)
  then
      _dayOfWeek:=7;
  end if;
  return _dayOfWeek;
END; 
--
CREATE OR REPLACE Function PYC_DATE_ADD_Workingdays(v_date date, v_nbdays integer) return date 
AS
   v_dayofweek integer;
   v_condition integer;
   v_calcul VARCHAR2(10);
   v_nbworkingdays integer;
   v_date_result date;
   v_result VARCHAR(255);
BEGIN
     select PYC_OurOwnDayOfWeek(v_date, 3) INTO v_dayofweek FROM DUAL;
     select v_nbdays+v_dayofweek  into v_condition from dual;
     if (v_condition<5)
     then
            v_nbworkingdays:=v_nbdays;
            v_calcul:='(normal)';
     ELSE
            v_calcul:='(formul)';
         select (floor((weekday(v_date)+v_nbdays)/5))*2+v_nbdays into v_nbworkingdays from dual;         
     end if;
     select DATE_ADD(v_date, INTERVAL v_nbworkingdays DAY) into v_date_result from dual;
      -- SELECT 'condition=' || v_dayofweek || '/' || v_condition || '   |   days=' || v_nbdays  || '/' || v_nbworkingdays || '  ==' || v_calcul || '==>  ' || DATE_FORMAT(v_date_result, '%W %D %M %Y')  INTO v_result;
     return v_date_result;
end;
$$

-- CREATE TABLE PYC_TEST_DATE (theDate DATE);
DELETE FROM PYC_TEST_DATE WHERE 1=1;
INSERT INTO PYC_TEST_DATE (theDate)  SELECT sysdate FROM DUAL;
INSERT INTO PYC_TEST_DATE (theDate)  SELECT DATE_ADD(SYSDATE, INTERVAL -2 DAY) FROM DUAL;
INSERT INTO PYC_TEST_DATE (theDate)  SELECT DATE_ADD(SYSDATE, INTERVAL -1 DAY) FROM DUAL;
INSERT INTO PYC_TEST_DATE (theDate)  SELECT DATE_ADD(SYSDATE, INTERVAL +1 DAY) FROM DUAL;
INSERT INTO PYC_TEST_DATE (theDate)  SELECT DATE_ADD(SYSDATE, INTERVAL +2 DAY) FROM DUAL;

The select to test can be this

SELECT DATE_FORMAT(theDate, '%W %D %M %Y') Original_date, 
       PYC_DATE_ADD_Workingdays (theDate, 1) D_plus_1, 
       PYC_DATE_ADD_Workingdays (theDate, 2) D_plus_2, 
         PYC_DATE_ADD_Workingdays (theDate, 3) D_plus_3, 
         PYC_DATE_ADD_Workingdays (theDate, 4) D_plus_4,  
         PYC_DATE_ADD_Workingdays (theDate, 5) D_plus_5,  
         PYC_DATE_ADD_Workingdays (theDate, 6) D_plus_6,  
         PYC_DATE_ADD_Workingdays (theDate, 7) D_plus_7  
from PYC_TEST_DATE ORDER BY theDate;

The result will be this

Original date D_plus_1 D_plus_2 D_plus_3 D_plus_4 D_plus_5 D_plus_6 D_plus_7
Monday 25th March 2024 2024-03-26 14:37:33 2024-03-27 14:37:33 2024-03-28 14:37:33 2024-03-29 14:37:33 2024-04-01 14:37:33 2024-04-02 14:37:33 2024-04-03 14:37:33
Tuesday 26th March 2024 2024-03-27 14:37:33 2024-03-28 14:37:33 2024-03-29 14:37:33 2024-04-01 14:37:33 2024-04-02 14:37:33 2024-04-03 14:37:33 2024-04-04 14:37:33
Wednesday 27th March 2024 2024-03-28 14:37:33 2024-03-29 14:37:33 2024-04-01 14:37:33 2024-04-02 14:37:33 2024-04-03 14:37:33 2024-04-04 14:37:33 2024-04-05 14:37:33
Thursday 28th March 2024 2024-03-29 14:37:33 2024-04-01 14:37:33 2024-04-02 14:37:33 2024-04-03 14:37:33 2024-04-04 14:37:33 2024-04-05 14:37:33 2024-04-08 14:37:33
Friday 29th March 2024 2024-04-01 14:37:33 2024-04-02 14:37:33 2024-04-03 14:37:33 2024-04-04 14:37:33 2024-04-05 14:37:33 2024-04-08 14:37:33 2024-04-09 14:37:33

Upvotes: 0

Albert Peschar
Albert Peschar

Reputation: 551

This can easily be done for an arbitrary amount of days with a recursive CTE:

WITH RECURSIVE a AS (
    SELECT
        CURRENT_DATE date,  -- Start date
        0 days
    UNION
    SELECT
        -- Always increment the date
        a.date + INTERVAL 1 DAY AS date,
        -- Increment the work day count only if it's not a weekend day
        a.days + (WEEKDAY(a.date + INTERVAL 1 DAY) < 5) AS days
    FROM a
    WHERE
        -- Keep going until the week day count reaches 10
        a.days < 10  -- Amount of days to add
)
SELECT MAX(date)
FROM a

In the example situation you would use a subquery:

SELECT
    (
        WITH RECURSIVE a AS (
            SELECT
                date_field date,
                0 days
            UNION
            SELECT
                a.date + INTERVAL 1 DAY AS date,
                a.days + (WEEKDAY(a.date + INTERVAL 1 DAY) < 5) AS days
            FROM a
            WHERE a.days < 5
        )
        SELECT MAX(date)
        FROM a
    ) AS final_date
FROM table_name

Upvotes: 2

Adonay28
Adonay28

Reputation: 31

My solution is to create a function that returns the calculated date, it will consider the consecutive weekends:

DELIMITER $$
CREATE FUNCTION `add_working_days_to_current_month`(ndays INT) RETURNS DATE
NO SQL 
BEGIN

    declare finalDate, startDate, originalFinalDate DATE;
    declare weekNumberStartDate, weekNumberEndDate, weekDiff INT;
    
    set startDate = DATE(CONCAT(YEAR(DATE_SUB(current_date(), INTERVAL 1 MONTH)),"-",MONTH(DATE_SUB(current_date(), INTERVAL 1 MONTH)),"-",DAY(LAST_DAY(DATE_SUB(current_date(), INTERVAL 1 MONTH)))));
    set weekNumberStartDate = WEEK(startDate);
    set finalDate = DATE_ADD(startDate, INTERVAL ndays DAY);
    set originalFinalDate = finalDate;
    set weekNumberEndDate = WEEK(finalDate);
    
    IF(weekNumberEndDate != weekNumberStartDate) THEN
        set weekDiff = (weekNumberEndDate - weekNumberStartDate) * 2;
        set finalDate = DATE_ADD(finalDate, INTERVAL weekDiff DAY);
    END IF;
    
    set weekNumberStartDate = WEEK(originalFinalDate);
    set weekNumberEndDate = WEEK(finalDate);
    IF(weekNumberEndDate != weekNumberStartDate) THEN
        set weekDiff = (weekNumberEndDate - weekNumberStartDate) * 2;
        set finalDate = DATE_ADD(finalDate, INTERVAL weekDiff DAY);
    END IF;
    
    IF(WEEKDAY(finalDate) IN (5, 6)) THEN
        set finalDate = DATE_ADD(finalDate, INTERVAL 2 DAY);
    END IF;
    
    
return finalDate;
END$$
DELIMITER ;

Basically i'm using the same logic as the accepted answer but acumulating the weekends. So for each weekend i will add 2 days and at the end i will if the result date is on weekend i will add 2 more

Upvotes: 0

quickes
quickes

Reputation: 506

Plus hollydays 1 or 2

select GREATEST(WEEKDAY(NOW()) - 4, 0) 'hollydays'

Upvotes: 0

5f8
5f8

Reputation: 1

WHERE datefield BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY
AND WEEKDAY(datefield) NOT IN (5,6);

Upvotes: -1

Cl&#233;ment Vannouque
Cl&#233;ment Vannouque

Reputation: 31

I did try your solution but faced a problem when using it with a larger interval (e.g 20 days). It works perfectly with little intervals though.

Example : for '2017-10-04' + 20 days, your algorithm return '2017-10-26'. It should be '2017-11-01' since we skip 4 weekends.

The numbers of days you add isn't calculated depending on the difference between the 2 week #, so the maximum days you can add is 2 and in my case, it should be 8 (4x2).

I modified your code to end up with this (I also add variables, much more convenient to modify)

SELECT 
@ID:='2017-10-04' as initial_date, -- the initial date in the right format to manipulate (add x day)
@DTA:=20 as days_to_add, -- number of days to add
@DA:= DATE_ADD(@ID, INTERVAL @DTA DAY) as date_add,
@LASTDAY := WEEKDAY(@DA) as last_day, -- the day (Monday, Tuesday...) corresponding to the initial date + number of days to add
@WEEK1 := DATE_FORMAT(@ID, '%v') as initial_date_week, -- format the initial date to match week mode 3 (Monday 1-53)
@WEEK2 := DATE_FORMAT(@DA, '%v') as added_date_week_nbr, -- the week # of the initial_date + number of days to add
@WEEKDIFF := @WEEK2 - @WEEK1 as week_difference, -- the difference between week 2 and week 1
DATE_ADD(@ID,
        INTERVAL @DTA + 
            if ( @WEEKDIFF > 0 or @LASTDAY in (5,6),
              2,
              0
              ) + 
             if (@WEEKDIFF > 1,
             @WEEKDIFF*2,
             0
             ) DAY
    ) AS FinalDate

The way I get my week numbers can seems weird but this is because I'm running this in France and my DB seems to be configured in a way that weeks are natively starting by Sunday, "%v" represent the 'mode 3' for weeks, you can check the MySQL documentation here for more details : https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html (ctrl + F > '%v')

I didn't implement the public holiday yet, but I'm thinking of adding X days in the calculation each times one of this day is in the period we're looking at.

According to my (few) tests, this should work. Let me know if not

Upvotes: 1

Blank
Blank

Reputation: 12378

Try this:

SELECT DATE_ADD(
    date_field,
    INTERVAL 5 + 
    IF(
        (WEEK(date_field) <> WEEK(DATE_ADD(date_field, INTERVAL 5 DAY)))
        OR (WEEKDAY(DATE_ADD(date_field, INTERVAL 5 DAY)) IN (5, 6)),
        2,
        0)
    DAY
    ) AS FinalDate
FROM `table_name`;

How it works:

  • Firstly, it will add 5 days on your date.
  • Secondly, when date_field and 5 days later are in two different weeks, it must be added additional 2 days.
  • Thirdly, when 5 days later is Sat or Sun, it must be added additional 2 days.

Upvotes: 10

Prashant Majhwar
Prashant Majhwar

Reputation: 180

try this out, should work nicely, basically loop through each of the days and check if they are saturday or sunday, ignore them.

https://social.technet.microsoft.com/wiki/contents/articles/30877.t-sql-extending-dateadd-function-to-skip-weekend-days.aspx

Upvotes: 0

Related Questions