计算 SQL 中 2 个日期之间的差异，不包括周末

Question

提问by lightmania

I would like to build an SQL query which calculates the difference between 2 dates, without counting the week-end days in the result.

我想构建一个 SQL 查询来计算两个日期之间的差异，而不计算结果中的周末天数。

Is there any way to format the dates to obtain this result ? For example for Oracle database :

有没有办法格式化日期以获得此结果？例如对于 Oracle 数据库：

select sysdate - creation_dttm from the_table

Answer 1

采纳答案by pollux1er

You should try with a function :

您应该尝试使用一个函数：

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);

Test :

测试：

SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
       TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;

Result :

结果：

| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |

Answer 2

回答by Dan from Bellevue WA

I have found several of the answers on this thread to not do what they claim. After some experimentation, testing and adjusting, I have this to contribute.

我在这个线程上找到了几个答案，但没有按照他们的要求去做。经过一些实验、测试和调整，我有这个贡献。

declare @firstdate Date
declare @seconddate Date

set @firstDate = convert(date, '2016-03-07')
set @seconddate = convert(date, '2016-04-04')


select (datediff(dd, @firstDate, @secondDate)) - 
    (( DateDiff(wk, @firstDate, @secondDate) * 2) - 
      case when datepart(dw, @FirstDate) = 7 then 1 else 0 end -
      case when datepart(dw, @secondDate) = 7 then -1 else 0 end)

Test harness included - you can just adjust the two dates and run your own tests. This assumes that the difference between two adjacent weekday dates is 1. If your country uses different days to signify weekend, then you will have to set the date-base accordingly so your "Saturday" is 7, and your "sunday" is 1.

包括测试工具 - 您可以调整两个日期并运行您自己的测试。这假设两个相邻的工作日之间的差异为 1。如果您的国家/地区使用不同的日期来表示周末，那么您必须相应地设置日期基准，以便您的“星期六”为 7，而您的“星期日”为 1。

Answer 3

回答by Brian Knight

From a previous post:

从以前的帖子：

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

Answer 4

回答by lightmania

I have found another way to do calculate the difference, by using only SQL :

我找到了另一种计算差异的方法，只使用 SQL：

select sysdate - creation_dttm
- 2 * (to_char(sysdate, 'WW') - to_char(creation_dttm, 'WW'))
from the_table

Answer 5

回答by WSHN

Here is an example:
There are four variables, the first two are self-explanatory, just enter the date in YYYYMMDD format, the 3rd one is to set the number of normal work days in a given week, so if a site works 6 days a week, set it to 6, five days a week enter 5, etc. Finally, the DATE_SEQ_NORML_FACTOR should be 1 when running against Oracle. This is to line up the Julian date to equal to 1 on Monday, 2 on Tuesday, etc when applying the MOD 7. Other DB will probably have different values between 0 and 6, so test it out before you use against other DBs.

下面是一个例子：
有四个变量，前两个是不言自明的，只需输入 YYYYMMDD 格式的日期，第三个是设置给定一周的正常工作日数，所以如果一个站点工作 6 天一周，将其设置为 6，每周 5 天输入 5，依此类推。最后，在针对 Oracle 运行时，DATE_SEQ_NORML_FACTOR 应为 1。这是为了在应用 MOD 7 时将儒略日期排列为周一 1、周二 2 等。其他 DB 可能具有 0 到 6 之间的不同值，因此在对其他 DB 使用之前先对其进行测试。

Here are the limitations: 1. This formula assumes the first day of the week is MONDAY. 2. This formula assumes all days within the same week are CONTINUOUS. 3. This formula will work ONLY when the two dates involved in the calculation falls on a week day or work day, eg. the "Start Date" on a SATURDAY when the location works only MON-FRI will not work.

以下是限制： 1. 此公式假定一周的第一天是 MONDAY。2. 该公式假设同一周内的所有天都是连续的。3. 此公式仅在计算中涉及的两个日期落在工作日或工作日时有效，例如。当该位置仅在周一至周五工作时，周六的“开始日期”将不起作用。

SELECT

  &&START_DATE_YYYYMMDD "Start Date", --in YYYYMMDD format

  &&END_DATE_YYYYMMDD "End Date", --in YYYYMMDD format

  &&WK_WORK_DAY_CNT "Week Work Day Count", --Number of work day per week

  &&DATE_SEQ_NORML_FACTOR "Normalization Factor", --set to 1 when run in Oracle

  CASE

  WHEN

    FLOOR( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR / 7 ) =

    FLOOR( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR / 7 )

  THEN(

    TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) -

    TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + 1

  )

  ELSE(

    (

      &&WK_WORK_DAY_CNT - MOD( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 ) + 1

    ) +

    MOD( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 ) +

    (

      (

        (

          TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) -

          MOD( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 )

        ) -

        (

          TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) +

          (

            7 -

            (

              MOD( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 )

            )

          )

        )

      ) / 7 * &&WK_WORK_DAY_CNT

    )

  ) END "Week Day Count"

FROM DUAL

Answer 6

回答by Brian Scott

I've updated @JOpuckman's function to take into account that different regions don't always have a weekend of Saturday and Sunday. Here's the code in case anyone else needs to apply this globally;

我已经更新了@JOpuckman 的函数，以考虑到不同地区并不总是有周六和周日的周末。这是代码，以防其他人需要在全球范围内应用它；

DECLARE @FirstDate DateTime
DECLARE @SecondDate DateTime

SET @FirstDate = '08-20-2012'
SET @SecondDate = '08-24-2012'

DECLARE @range INT;
DECLARE @WeekendDayNameStart VARCHAR(50) 
DECLARE @WeekendDayNameEnd VARCHAR(50)

SET @WeekendDayNameStart = 'FRIDAY'
SET @WeekendDayNameEnd = (
    SELECT CASE @WeekendDayNameStart
        WHEN 'SUNDAY' THEN 'MONDAY'
        WHEN 'MONDAY' THEN 'TUESDAY'
        WHEN 'TUESDAY' THEN 'WEDNESDAY'
        WHEN 'WEDNESDAY' THEN 'THURSDAY'
        WHEN 'THURSDAY' THEN 'FRIDAY'
        WHEN 'FRIDAY' THEN 'SATURDAY'
        WHEN 'SATURDAY' THEN 'SUNDAY'
    END
)

DECLARE @NumWorkDays INT

SET @range = DATEDIFF(DAY, @FirstDate, @SecondDate); 
SET @NumWorkDays = (
SELECT  
@range / 7 * 5 + @range % 7 - ( 
    SELECT COUNT(*)  
    FROM ( 
        SELECT 1 AS d 
        UNION ALL SELECT 2  
        UNION ALL SELECT 3  
        UNION ALL SELECT 4  
        UNION ALL SELECT 5  
        UNION ALL SELECT 6  
        UNION ALL SELECT 7 
        ) weekdays 
    WHERE d <= @range % 7  
    AND DATENAME(WEEKDAY, @SecondDate - d) IN (@WeekendDayNameStart, @WeekendDayNameEnd))
);

-- Calculate whether the current date is a working day
DECLARE @CurDateExtra INT
SET @CurDateExtra = 
(
    CASE DATENAME(WEEKDAY, @SecondDate)
    WHEN @WeekendDayNameStart THEN 0
    WHEN @WeekendDayNameEnd THEN 0
    ELSE 1
    END
)

SET @NumWorkDays = @NumWorkDays + @CurDateExtra
SELECT @NumWorkDays

Answer 7

回答by JOpuckman

We've incorporated the following logic into several reports with great success. Sorry, I no longer recall the source of this script to give them credit.

我们已将以下逻辑合并到多份报告中，并取得了巨大成功。对不起，我不再记得这个脚本的来源来给他们信任。

DECLARE @range INT;

SET @range = DATEDIFF(DAY, @FirstDate, @SecondDate); 
SET @NumWorkDays = (
    SELECT  
    @range / 7 * 5 + @range % 7 - ( 
        SELECT COUNT(*)  
        FROM ( 
            SELECT 1 AS d 
            UNION ALL SELECT 2  
            UNION ALL SELECT 3  
            UNION ALL SELECT 4  
            UNION ALL SELECT 5  
            UNION ALL SELECT 6  
            UNION ALL SELECT 7 
            ) weekdays 
        WHERE d <= @range % 7  
        AND DATENAME(WEEKDAY, @SecondDate - d) IN ('Saturday', 'Sunday'))
    );

Answer 8

回答by Tiago Goddard

There is a problem with @lightmania answer I think.

我认为@lightmania 的回答有问题。

Using values:

使用值：

select (TO_DATE('06/02/2014', 'DD/MM/YYYY') - 
        TO_DATE('04/02/2014', 'DD/MM/YYYY') - 
        2 * (to_char(TO_DATE('06/02/2014', 'DD/MM/YYYY'), 'WW') -
             to_char(TO_DATE('04/02/2014', 'DD/MM/YYYY'), 'WW'))) from dual;

Returned 0 instead of 2 that it should have returned.

返回 0 而不是它应该返回的 2。

Answer 9

回答by JBurton

Calculates the difference in days between the two dates
Calculates the difference in week numbers and year numbers, subtracts the week numbers and then multiplies the result by 2 to calculate number of non-workdays between the two dates. If year numbers are different calculation of 52 * year difference + week number.

计算两个日期之间的天数差
计算周数和年数的差值，减去周数，然后将结果乘以 2 以计算两个日期之间的非工作日数。如果年份数字不同，则计算 52 * 年份差异 + 周数。

((sysdate - ced.created_dt) + ((((to_char(ced.created_dt,'IW') - ((to_char(sysdate,'YY') - to_char(ced.created_dt,'YY'))* 52)) - to_char(to_char(sysdate,'IW')))) * 2)) duration_in_weekdays

Answer 10

回答by Ulises

You can try this:

你可以试试这个：

SELECT
    Id,
    DATEDIFF(d, datefrom, dateto) AS TotDays,
    DATEDIFF(wk, datefrom, dateto) AS Wkds,
    DATEDIFF(d, datefrom, dateto) - DATEDIFF(wk, datefrom, dateto) AS Days
FROM
    YOURTABLE

计算 SQL 中 2 个日期之间的差异，不包括周末

提问by lightmania

采纳答案by pollux1er

回答by Dan from Bellevue WA

回答by Brian Knight

回答by lightmania

回答by WSHN

回答by Brian Scott

回答by JOpuckman

回答by Tiago Goddard

回答by JBurton

回答by Ulises

相关推荐

最近更新

标签

计算 SQL 中 2 个日期之间的差异，不包括周末

提问by lightmania

采纳答案by pollux1er

回答by Dan from Bellevue WA

回答by Brian Knight

回答by lightmania

回答by WSHN

回答by Brian Scott

回答by JOpuckman

回答by Tiago Goddard

回答by JBurton

回答by Ulises

相关推荐

SQL 试图将行展平为列

SQL 选择数据库中的表列表

SQL 如何使用oracle视图识别Oracle中的高负载SQL？

SQL 如何在 DO 块中执行选择查询？

相关推荐

最近更新

标签