SQL中怎么计算工作日

SQL中怎么计算工作日,针对这个问题,这篇文章详细介绍了相对应的分析和解答,希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

创新互联是一家集网站建设,镇宁企业网站建设,镇宁品牌网站建设,网站定制,镇宁网站建设报价,网络营销,网络优化,镇宁网站推广为一体的创新建站企业,帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿,时刻以成就客户成长自我,坚持不断学习、思考、沉淀、净化自己,让我们为更多的企业打造出实用型网站。

CREATE FUNCTION Time_Span_of_minutes(  

start_day DATETIME,  

end_day DATETIME)  

RETURNS FLOAT 

BEGIN 

            -- 返回按分钟计算两段时间的间隔,采用逐日靠近的方法求解,一天按8小时480分钟计算,周末不计,不考虑法定节假日。  

            -- 如果起始日期在周末,则计算当天时间。  

            DECLARE minutes FLOAT;   

            DECLARE next_day DATETIME;  

            SET minutes=0.0;  

            SET next_day=start_day;  

            IF(TIMESTAMPDIFF(DAY,start_day,end_day)<1.0) THEN 

                    SET minutes=minutes+TIMESTAMPDIFF(MINUTE,start_day,end_day);  

            ELSE 

                    WHILE TIMESTAMPDIFF(DAY,next_day,end_day)>=1.0 DO  

                            IF ((DAYOFWEEK(next_day)=7) OR (DAYOFWEEK(next_day)=1)) THEN 

                                    SET next_day=next_day+INTERVAL 1 DAY;  

                            ELSE 

                                    SET next_day=next_day+INTERVAL 1 DAY;  

                                    SET minutes=minutes+480.0;  

                            END IF;  

                    END WHILE;  

                    SET minutes=minutes+TIMESTAMPDIFF(MINUTE,next_day,end_day);  

                    IF ((DAYOFWEEK(start_day)=7) OR (DAYOFWEEK(start_day)=1)) THEN 

                            SET minutes=minutes+TIMESTAMPDIFF(MINUTE,start_day,CONVERT(CONCAT(SUBSTRING(start_day FROM 1 FOR 10),' 17:30:00'),DATETIME));  

                    END IF;  

            END IF;  

            RETURN minutes;  

END; 

CREATE FUNCTION Time_Span_of_minutes(

start_day DATETIME,

end_day DATETIME)

RETURNS FLOAT

BEGIN

                     -- 返回按分钟计算两段时间的间隔,采用逐日靠近的方法求解,一天按8小时480分钟计算,周末不计,不考虑法定节假日。

                     -- 如果起始日期在周末,则计算当天时间。

                     DECLARE minutes FLOAT;

                     DECLARE next_day DATETIME;

                     SET minutes=0.0;

                     SET next_day=start_day;

                     IF(TIMESTAMPDIFF(DAY,start_day,end_day)<1.0) THEN

                                   SET minutes=minutes+TIMESTAMPDIFF(MINUTE,start_day,end_day);

                     ELSE

                                   WHILE TIMESTAMPDIFF(DAY,next_day,end_day)>=1.0 DO

                                                 IF ((DAYOFWEEK(next_day)=7) OR (DAYOFWEEK(next_day)=1)) THEN

                                                               SET next_day=next_day+INTERVAL 1 DAY;

                                                 ELSE

                                                               SET next_day=next_day+INTERVAL 1 DAY;

                                                               SET minutes=minutes+480.0;

                                                 END IF;

                                   END WHILE;

                                   SET minutes=minutes+TIMESTAMPDIFF(MINUTE,next_day,end_day);

                                   IF ((DAYOFWEEK(start_day)=7) OR (DAYOFWEEK(start_day)=1)) THEN

                                                 SET minutes=minutes+TIMESTAMPDIFF(MINUTE,start_day,CONVERT(CONCAT(SUBSTRING(start_day FROM 1 FOR 10),' 17:30:00'),DATETIME));

                                   END IF;

                     END IF;

                     RETURN minutes;

END;

关于SQL中怎么计算工作日问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注创新互联行业资讯频道了解更多相关知识。


文章标题:SQL中怎么计算工作日
标题链接:http://myzitong.com/article/ighcds.html