计算SQL中2个日期之间的差值,不包括周末 [英] Calculate difference between 2 dates in SQL, excluding weekend days
本文介绍了计算SQL中2个日期之间的差值,不包括周末的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想构建一个SQL查询来计算两个日期之间的差,而不计算结果中的周末天。
有什么方法可以格式化日期以获得此结果?例如对于Oracle数据库:
从_table
$中选择sysdate-creation_dttm p $ p>
解决方案您应尝试使用以下函数:
创建函数TOTAL_WEEKDAYS(date1 DATE,date2 DATE)
返回INT
RETURN ABS(DATEDIFF(date2,date1))+ 1
-ABS(DATEDIFF(ADDDATE(date2) ,间隔1-DAYOFWEEK(date2)DAY),
ADDDATE(date1,间隔1-DAYOFWEEK(date1)DAY)))/ 7 * 2
-(DAYOFWEEK(IF(date1< date2,date1 ,date2))= 1)
-(DAYOFWEEK(IF(date1> date2,date1,date2))= 7);
测试:
SELECT TOTAL_WEEKDAYS('2013-08-03','2013-08-21')工作日1,
TOTAL_WEEKDAYS('2013-08-21','2013-08-03')工作日2;
结果:
| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
| 13 | 13 |
I would like to build an SQL query which calculates the difference between 2 dates, without counting the week-end days in the result.
Is there any way to format the dates to obtain this result ? For example for Oracle database :
select sysdate - creation_dttm from the_table
解决方案You should try with a function :
CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE) RETURNS INT RETURN ABS(DATEDIFF(date2, date1)) + 1 - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY), ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2 - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1) - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);
Test :
SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1, TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;
Result :
| WEEKDAYS1 | WEEKDAYS2 | ------------------------- | 13 | 13 |
这篇关于计算SQL中2个日期之间的差值,不包括周末的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文