TSQL中日期范围内的工作日数 [英] Numbers of weekdays in a date range in TSQL

问题描述

这比看起来要难.我需要一个函数来计算日期范围内给定工作日的数量.我不需要任何循环或递归SQL.数以百万计的示例就是这样做的.我需要一个快速的函数来进行计算.

This is harder than it looks. I need a function that calculates the numbers of a given weekday in a date range. I don't want any loops or recursive SQL. There are millions of examples doing just that. I need a fast function for calculation.

该函数的输入将是工作日，fromdata，迄今为止

Input of the function will be weekday, fromdata, todate

-- counting fridays
set datefirst 1
SELECT dbo.f_countweekdays(5, '2011-07-01', '2011-07-31'),
dbo.f_countweekdays(5, '2011-07-08', '2011-07-15'),
dbo.f_countweekdays(5, '2011-07-09', '2011-07-15'),
dbo.f_countweekdays(5, '2011-07-09', '2011-07-14')

预期结果:

5, 2, 1, 0

推荐答案

@Mikael Eriksson有一个绝妙的主意，但是他的实现似乎有些复杂.

@Mikael Eriksson has got a wonderful idea, but his implementation seems a bit overcomplicated.

这是我想出的(我想强调一下，它基于

Here's what I've come up with (and I'd like to stress that it is based on the solution by @Mikael, to whom the main credit should go):

ALTER FUNCTION dbo.f_countweekdays (@Dow int, @StartDate datetime, @EndDate datetime)
RETURNS int
AS BEGIN
  RETURN (
    SELECT
      DATEDIFF(wk, @StartDate, @EndDate)
      - CASE WHEN DATEPART(dw, @StartDate) > @Dow THEN 1 ELSE 0 END
      - CASE WHEN DATEPART(dw, @EndDate)   < @Dow THEN 1 ELSE 0 END
      + 1
  )
END

---

更新

正如Mikael在其答案的注释线程中正确指出的那样，为了使上述解决方案正确运行，必须将DATEFIRST设置设置为7(星期日).尽管我找不到此文档，但快速测试发现DATEDIFF(wk)忽略了实际的DATEFIRST设置，实际上返回了以周为单位的差值，好像DATEFIRST始终设置为7.同时，DATEPART(dw)确实尊重了DATEFIRST ，因此将DATEFIRST设置为7以外的其他值，这两个函数将返回不一致的结果.

As Mikael has correctly noted in his answer's comment thread, in order for the above solution to work correctly the DATEFIRST setting must be set to 7 (Sunday). Although I couldn't find this documented, a quick test revealed that DATEDIFF(wk) disregards the actual DATEFIRST setting and indeed returns the difference in weeks as if DATEFIRST was always set to 7. At the same time DATEPART(dw) does respect DATEFIRST, so with DATEFIRST set to a value other than 7 the two functions return mutually inconsistent results.

因此，必须对上述脚本进行修改，以便在计算DATEDIFF(wk)时考虑DATEFIRST设置的不同值.令人高兴的是，在我看来，此修复程序似乎并未使解决方案变得比以前复杂得多.不过要自己判断:

Therefore, the above script must be amended in order to account for different values of the DATEFIRST setting when calculating DATEDIFF(wk). Happily, the fix doesn't seem to have made the solution much more complicated than before, in my opinion. Judge for yourself, though:

ALTER FUNCTION dbo.f_countweekdays (@Dow int, @StartDate datetime, @EndDate datetime)
RETURNS int
AS BEGIN
  RETURN (
    SELECT
      DATEDIFF(wk, DATEADD(DAY, -@@DATEFIRST, @StartDate),
                   DATEADD(DAY, -@@DATEFIRST, @EndDate))
      - CASE WHEN DATEPART(dw, @StartDate) > @Dow THEN 1 ELSE 0 END
      - CASE WHEN DATEPART(dw, @EndDate)   < @Dow THEN 1 ELSE 0 END
      + 1
  )
END

^{编辑:正如某人建议}

^{Edited: both -@@DATEFIRST % 7 entries have been simplified to just -@@DATEFIRST, as someone suggested here.}

这篇关于TSQL中日期范围内的工作日数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！