在 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屋！