Oracle查询以获取两个日期之间的工作日数 [英] Oracle query to get the number of business days between 2 dates

问题描述

我们正在使用Oracle 11.

We are using Oracle 11.

在我们的CASE WHEN语句中，我需要检查两个日期之间的天数是否大于3个工作日(因此不包括周末和节假日).

In our CASE WHEN statement, I need to check if the number of days between the 2 dates are > 3 business days (so excluding weekends and holidays).

所以，如果

Date1 = 1/21/19(星期一)

Date1 = 1/21/19 (Monday)

Date2 = 1/18/19(星期五)

Date2 = 1/18/19 (Friday)

日期1和日期2之间的工作日数是1天，而不是3天.

The number of business days in between Date1 and Date2 is 1 day, not 3 days.

CASE WHEN TO_DATE(SETTLEMENT_DATE, 'YYYY-MM-DD') - TO_DATE(TRADE_DATE, 
'YYYY-MM-DD')  > 3  THEN 0  --> this includes weekend and holidays
WHEN CODE = 1 THEN 1
WHEN CODE =2 THEN 2
ELSE 3
END AS MyColumn

谢谢.

推荐答案

您不需要使用分层查询，并且可以独立于NLS设置使用TRUNC( date_value, 'IW' )进行查询，以查找ISO周的开始时间，即总是一个星期一.

You do not need to use a hierarchical query and can do it independent of the NLS settings using TRUNC( date_value, 'IW' ) to find the start of the ISO week, which is always a Monday.

( TRUNC( end_date, 'IW' ) - TRUNC( start_date, 'IW' ) ) * 5 / 7

将找到包含开始日期的ISO周开始与包含结束日期的ISO周开始之间的工作天数.

Will find the number of working days between the start of the ISO week containing the start date and the start of the ISO week containing the end date.

然后只需添加最后一周的工作日:

Then just add the working days of the final week:

+ LEAST( TRUNC( end_date   ) - TRUNC( end_date,   'IW' ) + 1, 5 )

并减去开始日期之前的第一个ISO周的天数:

And subtract the days of the first ISO week before the start date:

- LEAST( TRUNC( start_date ) - TRUNC( start_date, 'IW' ), 5 )

哪一个放在一起会给出:

Which when put together gives:

Oracle设置

CREATE TABLE table_name ( start_date, end_date ) AS
SELECT DATE '2018-12-30' + LEVEL, DATE '2019-01-10' 
FROM DUAL
CONNECT BY LEVEL <= 11

查询:

SELECT TO_CHAR( start_date, 'YYYY-MM-DD "("DY")"') AS start_date,

       ( TRUNC( end_date, 'IW' ) - TRUNC( start_date, 'IW' ) ) * 5 / 7
       + LEAST( TRUNC( end_date   ) - TRUNC( end_date,   'IW' ) + 1, 5 )
       - LEAST( TRUNC( start_date ) - TRUNC( start_date, 'IW' ), 5 )
         AS Num_Week_Days
FROM   table_name;

输出:

START_DATE       | NUM_WEEK_DAYS
:--------------- | ------------:
2018-12-31 (MON) |             9
2019-01-01 (TUE) |             8
2019-01-02 (WED) |             7
2019-01-03 (THU) |             6
2019-01-04 (FRI) |             5
2019-01-05 (SAT) |             4
2019-01-06 (SUN) |             4
2019-01-07 (MON) |             4
2019-01-08 (TUE) |             3
2019-01-09 (WED) |             2
2019-01-10 (THU) |             1

db<>小提琴此处

这篇关于Oracle查询以获取两个日期之间的工作日数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！