MySQL函数将多个工作日添加到DATETIME [英] MySQL Function to add a number of working days to a DATETIME

问题描述

我需要一个MySQL函数，该函数将允许我经过多个工作日(星期一至星期五)和开始日期DATE或DATETIME(与实现无关紧要)，并使其返回一个新的DATE或DATETIME，未来很多工作日.

I need a MySQL Function that will allow me to pass a number of working days (Monday - Friday) and a start DATE or DATETIME (doesn't matter for my implementation), and have it return a new DATE or DATETIME that many work days in the future.

示例:SELECT AddWorkDays(10, "2013-09-01")假设"2013-09-01"是星期一，则返回"2013-09-16".

Example: SELECT AddWorkDays(10, "2013-09-01") returns "2013-09-16" assuming "2013-09-01" is a Monday.

类似地:SELECT AddWorkDays(-10, "2013-09-16")返回"2013-09-01"

Similarly: SELECT AddWorkDays(-10, "2013-09-16") returns "2013-09-01"

我找到了此函数用于MSSQL数据库(我认为)，正是我所需要的，除了它不在MySQL中.我试图将其手动转换为MySQL语法，并且到此为止:

I found this function for an MSSQL database (I think) that is exactly what I need except its not in MySQL. I tried to manually convert it into MySQL syntax and got about this far:

DROP FUNCTION IF EXISTS AddWorkDays;
DELIMITER $$
CREATE FUNCTION AddWorkDays
(
    WorkingDays INT,
    StartDate DATE
)
RETURNS DATE

BEGIN
    DECLARE Count INT;
    DECLARE i INT;
    DECLARE NewDate DATE;
    SET Count = 0;
    SET i = 0;

    WHILE (i < WorkingDays) DO
        BEGIN
            SET Count = Count + 1;
            SET i = i + 1;
            WHILE DAYOFWEEK(ADDDATE(StartDate, Count)) IN (1,7) DO
                BEGIN
                    SET Count = Count + 1;
                END;
            END WHILE;
        END;
    END WHILE;

    SET NewDate = ADDDATE(StartDate, Count);
    RETURN NewDate;

END;
$$

DELIMITER ;

我最终收到一个错误:

Error 1415: Not allowed to return a result set from a function

我似乎无法弄清楚它试图返回结果集的确切位置.

I can't seem to figure out where exactly it is trying to return a result set.

我的语法是否有错误?有更好的解决方案吗?

Is there an error in my syntax? Are there any better solutions?

谢谢！

编辑

看来MySQL没有DATEPART或DATEADD函数.我在文档中看到它们具有ADDDATE和DAYOFWEEK.更新了代码以表示这一点.我还将SELECT语句更改为SET(现在就知道为什么我得到了原始错误)

It appears MySQL doesn't have a DATEPART or DATEADD function. I see in the documentation that they have ADDDATE and DAYOFWEEK. Updated the code to represent this. I also changed the SELECT statements to SET (Makes sense now why I was getting the original error)

结果是，我尝试通过CF使用该函数运行查询时收到新错误

As a result I get a new error when attempting to run a query using the function via CF

[Table (rows 1 columns ADDWORKDAYS(10,"2013-09-01")): [ADDWORKDAYS(10,"2013-09-01"): coldfusion.sql.QueryColumn@7a010] ] is not indexable by ADDWORKDAYS(10

推荐答案

这是具有mysql语法的新功能:

This is new function with mysql syntax:

DROP FUNCTION IF EXISTS AddWorkDays;
DELIMITER $$
CREATE FUNCTION AddWorkDays
(
    WorkingDays INT,
    StartDate DATETIME
)
RETURNS DATETIME

BEGIN
    DECLARE Count INT;
    DECLARE i INT;
    DECLARE NewDate DATETIME;
    SET Count = 0;
    SET i = 0;

    WHILE (i < WorkingDays) DO
        BEGIN
            SELECT Count + 1 INTO Count;
            SELECT i + 1 INTO i;
            WHILE DAYOFWEEK(DATE_ADD(StartDate,INTERVAL Count DAY)) IN (1,7) DO
                BEGIN
                    SELECT Count + 1 INTO Count;
                END;
            END WHILE;
        END;
    END WHILE;

    SELECT DATE_ADD(StartDate,INTERVAL Count DAY) INTO NewDate;
    RETURN NewDate;

END;
$$

DELIMITER ;

这篇关于MySQL函数将多个工作日添加到DATETIME的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！