计算假期:Oracle中给定日期范围查询中的周六和周日数

问题描述

我要计算假期:Oracle中给定日期范围查询中的星期六和星期日的数量.

I want to calculate holidays: the number of Saturdays and Sundays within the given date range query in Oracle.

推荐答案

您可以使用 ROW GENERATOR 技术首先生成给定范围的日期，然后仅计算 SATURDAYs和周日.

You could use the ROW GENERATOR technique to first generate the dates for a given range, and then count only the SATURDAYs and SUNDAYs.

例如，此查询将为我提供2014年1月1日至2014年12月31日之间的星期六和星期日的总数-

For example, this query will give me the total count of saturdays and sundays between 1st Jan 2014 and 31st Dec 2014 -

SQL> WITH DATA AS
  2    (SELECT to_date('01/01/2014', 'DD/MM/YYYY') date1,
  3      to_date('31/12/2014', 'DD/MM/YYYY') date2
  4    FROM dual
  5    )
  6  SELECT SUM(holiday) holiday_count
  7  FROM
  8    (SELECT
  9      CASE
 10        WHEN TO_CHAR(date1+LEVEL-1, 'DY','NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT', 'SUN')
 11        THEN 1
 12        ELSE 0
 13      END holiday
 14    FROM data
 15      CONNECT BY LEVEL <= date2-date1+1
 16    )
 17  /

HOLIDAY_COUNT
-------------
          104

SQL>

这篇关于计算假期:Oracle中给定日期范围查询中的周六和周日数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！