希望SQL在2个日期之间获取天数，并在Oracle中排除星期六和星期日 [英] Want a SQL to get days between 2 date and exclude Saturday and Sunday in Oracle

问题描述

正如标题所述，我想计算Oracle中两个日期之间的天数，并排除周末(周六和周日)，对此我一无所知，有人可以帮忙给出一个示例SQL吗?

As the title said, I want to calculate days between 2 dates and exclude weekend(Saturday and Sunday) in Oracle, I have no idea about this at all, can anyone help give an example SQL?

推荐答案

尝试一下.根据您的要求更改开始日期和结束日期.

Try this. Change start_date and end_date as per your requirement.

如果您想要工作日数，请使用

If you want number of weekdays, use

  WITH test_data AS
  (
     SELECT TO_DATE('01-JAN-2014','DD-MON-YYYY') AS start_date,
            TO_DATE('31-DEC-2014','DD-MON-YYYY') AS end_date  
       FROM dual
  ), 
all_dates AS 
( SELECT td.start_date, td.end_date, td.start_date + LEVEL-1 as week_day
      FROM test_data td
   CONNECT BY td.start_date + LEVEL-1 <=  td.end_date)

   select count(*) from all_dates
    WHERE to_char(week_day, 'dy', 'nls_date_language=AMERICAN') NOT IN ('sun' , 'sat')

如果要显示日期，请在select子句中使用

If you want the dates displayed, in the select clause, use

select week_day from all_dates

工作原理:

1. 使用Oracle中的connect by/level，它将开始日期添加1天到开始日期，直到达到end_date.
2. 它将删除saturday或sunday
的日期

1. It start adding 1 day to start_date until you reach end_date, by using connect by/level in Oracle.
2. It removes the days which are saturday or sunday

PS:为此参考 answer

这篇关于希望SQL在2个日期之间获取天数，并在Oracle中排除星期六和星期日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！