Oracle SQl Dev，如何计算两个日期之间的星期几 [英] Oracle SQl Dev, how to calc num of weekdays between 2 dates

问题描述

有谁知道如何计算两个日期字段之间的工作日数量？我使用oracle sql开发人员。我需要查找多个开始和结束日期之间的平均工作日。所以我需要得到每个记录的天数，所以我可以平均他们。这个东西可以在我的查询的 SELECT 部分中做一行吗？

Does anyone know how can I calculate the number of weekdays between two date fields? I'm using oracle sql developer. I need to find the average of weekdays between multiple start and end dates. So I need to get the count of days for each record so I can average them out. Is this something that can be done as one line in the SELECT part of my query?

推荐答案

这个答案与Nicholas类似，这不是一个惊喜，因为你需要一个带有 CONNECT BY 的子查询来分离日期列表。然后可以在检查一周中的某一天的同时计算日期。这里的区别是，它显示如何获取每一行结果的工作日计数值：

This answer is similar to Nicholas's, which isn't a surprise because you need a subquery with a CONNECT BY to spin out a list of dates. The dates can then be counted while checking for the day of the week. The difference here is that it shows how to get the weekday count value on each line of the results:

SELECT
  FromDate,
  ThruDate,
  (SELECT COUNT(*)
     FROM DUAL
     WHERE TO_CHAR(FromDate + LEVEL - 1, 'DY') NOT IN ('SAT', 'SUN')
     CONNECT BY LEVEL <= ThruDate - FromDate + 1
  ) AS Weekday_Count
FROM myTable

计数包括在内，意味着它包括 FromDate 和 ThruDate 。此查询假定您的日期没有时间组件;如果他们，您需要 TRUNC 子查询中的日期列。

The count is inclusive, meaning it includes FromDate and ThruDate. This query assumes that your dates don't have a time component; if they do you'll need to TRUNC the date columns in the subquery.

这篇关于Oracle SQl Dev，如何计算两个日期之间的星期几的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！