CODEWITHSUNDEEP
mysqlsql
Danielphillips
Danielphillips

Reputation: 51

SQL: DATE_ADD(date,INTERVAL expr type) skip weekends

I'm currently using DATE_ADD(date,INTERVAL expr type) to set a due date as a trigger in a mySQL Database.

What I'm wanting to know is if it is possible to skip weekends (Saturday, Sunday) as part of the trigger.

Upvotes: 0

Views: 1617

Answers (2)

tarheel
tarheel

Reputation: 4797

This function simply creates a list of dates starting at the date given in the arguments, and then figures out which date is x number of days (the interval) out while disregarding days 1 and 7 (which are Sunday and Saturday respectively on SQL Server).

CREATE FUNCTION [dbo].[udf_days_add_no_wknd] 
    (
    @start_date date
    , @interval int
    )
RETURNS date
AS
BEGIN

declare @answer date

; with dates as 
    (
        select @start_date as date_val
        union all
        select dateadd(d, 1, date_val) as date_val
        from dates
        where date_val < dateadd(d, @interval * 10, @start_date)
    )
    , final as
    (
        select top 1 lead(ld.date_val, @interval, NULL) over (order by ld.date_val asc) as new_date_val
        from dates as ld
        where 1=1
        and datepart(dw, ld.date_val) not in (1,7) --eliminating weekends
    )
select @answer = (select new_date_val from final)

return @answer

END

It is worth nothing that this solution is dependent on having SQL Server 2012 or later, considering the use of the lead() function.

Upvotes: 0

Serge Seredenko
Serge Seredenko

Reputation: 3541

You'd have to create an own function for doing that. You can look how to do that in this answer, for example (just use function instead of procedure). As for how to write such a function, here's a working algorithm. The code is quite straightforward: it loops through days and skips weekends.

CREATE FUNCTION `DAYSADDNOWK`(addDate DATE, numDays INT) RETURNS date
BEGIN
    IF (WEEKDAY(addDate)=5) THEN
        SET addDate=DATE_ADD(addDate, INTERVAL 1 DAY);
    END IF;
    IF (WEEKDAY(addDate)=6) THEN
        SET addDate=DATE_ADD(addDate, INTERVAL 1 DAY);
    END IF;
    WHILE numDays>0 DO
       SET addDate=DATE_ADD(addDate, INTERVAL 1 DAY);
       IF (WEEKDAY(addDate)=5) THEN
           SET addDate=DATE_ADD(addDate, INTERVAL 1 DAY);
       END IF;
       IF (WEEKDAY(addDate)=6) THEN
           SET addDate=DATE_ADD(addDate, INTERVAL 1 DAY);
       END IF;
       SET numDays=numDays-1;
    END WHILE;
    RETURN addDate;
END

Currently SELECT DAYSADDNOWK(CURDATE(), 5) yields 2016-03-07, which is correct.

Of course you only can use it with days, so no arbitrary interval, but your question mentioned date datatype, and I don't quite see how one could add a month not counting working days.

Upvotes: 1

Related Questions