对于这样的事情,我倾向于维护一个日历表,其中还包括银行节假日等。
我使用的脚本如下(请注意,我没有编写它,但忘记了我在哪里找到它)
SET DATEFIRST 1
SET NOCOUNT ON
GO
CREATE FUNCTION ISOweek ( @DATE DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @ISOweek INT
SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104')
IF ( @ISOweek = 0 )
SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4)) + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1
IF ( ( DATEPART(mm, @DATE) = 12 )
AND ( ( DATEPART(dd, @DATE) - DATEPART(dw, @DATE) ) >= 28 )
)
SET @ISOweek = 1
RETURN(@ISOweek)
END
GO
CREATE FUNCTION fnDLA_GetEasterdate ( @year INT )
RETURNS CHAR(8)
AS
BEGIN
DECLARE @A INT ,
@B INT ,
@C INT ,
@D INT ,
@E INT ,
@F INT ,
@G INT ,
@H INT ,
@I INT ,
@K INT ,
@L INT ,
@M INT ,
@O INT ,
@R INT
SET @A = @YEAR % 19
SET @B = @YEAR / 100
SET @C = @YEAR % 100
SET @D = @B / 4
SET @E = @B % 4
SET @F = ( @B + 8 ) / 25
SET @G = ( @B - @F + 1 ) / 3
SET @H = ( 19 * @A + @B - @D - @G + 15 ) % 30
SET @I = @C / 4
SET @K = @C % 4
SET @L = ( 32 + 2 * @E + 2 * @I - @H - @K ) % 7
SET @M = ( @A + 11 * @H + 22 * @L ) / 451
SET @O = 22 + @H + @L - 7 * @M
IF @O > 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END
RETURN @R
END
GO
CREATE TABLE MyDateTable
(
FullDate DATETIME NOT NULL
CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED ,
Period INT ,
ISOWeek INT ,
WorkingDay VARCHAR(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
DECLARE @DateFrom DATETIME ,
@DateTo DATETIME ,
@Period INT
SET @DateFrom = CONVERT(DATETIME, '20000101')
SET @DateTo = CONVERT(DATETIME, '20991231')
WHILE @DateFrom <= @DateTo
BEGIN
SET @Period = CONVERT(INT, LEFT(CONVERT(VARCHAR(10), @DateFrom, 112), 6))
INSERT MyDateTable
( FullDate ,
Period ,
ISOWeek
)
SELECT @DateFrom ,
@Period ,
dbo.ISOweek(@DateFrom)
SET @DateFrom = DATEADD(dd, +1, @DateFrom)
END
GO
UPDATE MyDateTable
SET WorkingDay = 'B'
WHERE FullDate = DATEADD(dd, -2, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate))))
OR FullDate = DATEADD(dd, +1, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate))))
GO
UPDATE MyDateTable
SET WorkingDay = 'B'
WHERE FullDate IN ( SELECT MIN(FullDate)
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 1
AND DATEPART(dw, FullDate) NOT IN ( 6, 7 )
GROUP BY DATEPART(yy, FullDate) )
OR FullDate IN ( SELECT MIN(FullDate)
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 5
AND DATEPART(dw, FullDate) = 1
GROUP BY DATEPART(yy, FullDate) )
OR FullDate IN ( SELECT MAX(FullDate)
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 5
AND DATEPART(dw, FullDate) = 1
GROUP BY DATEPART(yy, FullDate) )
OR FullDate IN ( SELECT MAX(FullDate)
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 8
AND DATEPART(dw, FullDate) = 1
GROUP BY DATEPART(yy, FullDate) )
OR FullDate IN ( SELECT CASE WHEN DATEPART(dw, FullDate) IN ( 6, 7 ) THEN DATEADD(dd, +2, FullDate)
ELSE FullDate
END
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 12
AND DATEPART(dd, FullDate) IN ( 25, 26 ) )
GO
UPDATE MyDateTable
SET WorkingDay = 'N'
WHERE DATEPART(dw, FullDate) IN ( 6, 7 )
GO
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
SET NOCOUNT OFF
一旦您创建了表格,找到工作日的数量就非常容易:
SELECT COUNT(FullDate) AS WorkingDays
FROM dbo.tbl_WorkingDays
WHERE WorkingDay = 'Y'
AND FullDate >= CONVERT(DATETIME, '10/01/2013', 103)
AND FullDate < CONVERT(DATETIME, '15/01/2013', 103)
请注意,此脚本包含英国银行假期,我不确定您所在的地区。