在Oracle SQL中计算工作日(无功能或过程) [英] Calculate business days in Oracle SQL(no functions or procedure)

问题描述

我正在尝试计算Oracle select中两个日期之间的工作日.我的意思是，我的计算得出的大多数结果对于给定的日期都是正确的(我将其与excel中的NETWORKDAYS进行了比较)，但有时它的范围从2天到-2天不等-我不知道为什么...

I am trying to calculate business days between two dates in Oracle select. I got to the point when my calculation gives most results correct for given dates (I compare it with NETWORKDAYS in excel) but sometimes it varies from 2 days to -2 days - and I don't know why...

这是我的代码:

SELECT
((to_char(CompleteDate,'J') - to_char(InstallDate,'J'))+1) - (((to_char(CompleteDate,'WW')+ (52 * ((to_char(CompleteDate,'YYYY') - to_char(InstallDate,'YYYY'))))) - to_char(InstallDate,'WW'))*2) as BusinessDays
FROM TABLE

谢谢！

推荐答案

最后的解决方案:

SELECT OrderNumber, InstallDate, CompleteDate,
  (TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 - 
  ((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
  (CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
  (CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as BusinessDays
FROM Orders
ORDER BY OrderNumber;

感谢您的所有答复！

这篇关于在Oracle SQL中计算工作日(无功能或过程)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！