Sql server 查詢指定時間區間工作日數、休息日數等日期操作

1、查詢指定時間區間的工作日

這個主要難點是法定節假日，國家的法定節假日每年都不一樣，還涉及到調休，所以我們設計一個假日表。主要欄位有年份，類型（是否調休），假期日期。如下：

 CREATE TABLE [dbo].[Holidays](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Holiday] [datetime2](7) NULL,--假期日期

[YearS] [char](4) NULL,--年份
[daytype] [int] NULL--類型

）

添加好當年的假期和調休日期

寫個方法計算出除開法定假日的工作日

ALTER FUNCTION [dbo].[GetWorkerDays]

(

-- Add the parameters for the function here

 @StartTime DATETIME, -- 起始時間

 @EndTime DATETIME -- 結束時間，查詢預設小於此時間

)

RETURNS INT

AS

BEGIN 

DECLARE @Total INT;

DECLARE @Temp INT;

DECLARE @Days INT;

DECLARE @Index INT;

SET @Days = DATEDIFF(DAY,@StartTime,@EndTime);

SET @Index = 0;

SET @Temp = 0;

SET @Total = 0;

WHILE @Index < @Days BEGIN 

SET @Temp = DatePart(WEEKDAY,DATEADD(DAY,@Index,@StartTime));

IF @Temp > 1 AND @Temp < 7 BEGIN 

SET @Total = @Total + 1;

END 

SET @Index = @Index + 1;

END 

RETURN ISNULL(@Total,0)

END

 

執行這個表值函數後加上調休日和減去法定假日就是工作日了，大家可以再寫一個存儲過程。

2，計算指定日期段的休息日

這個跟那個相反，就是星期天加上法定假日在減去調休日

我們寫一個函數

ALTER FUNCTION GetRestDays

(

@StartTime DATETIME2,

@EndTime DATETIME2

)

RETURNS INT

AS

BEGIN

DECLARE @LegalRest INT --法定假期

DECLARE @AdjustmentDay INT--調休上班時間

DECLARE @SurplusDay INT --剩餘工作日

DECLARE @CountDay INT --總共天數

 

SELECT @LegalRest=COUNT(0) FROM dbo.Holidays WHERE daytype=1 AND  YearS=YEAR(GETDATE()) AND MONTH(Holiday)=MONTH(GETDATE()) 

AND Holiday>=@StartTime AND Holiday<=@EndTime

 

SELECT  @AdjustmentDay=COUNT(0) FROM dbo.Holidays WHERE daytype=2 AND  YearS=YEAR(GETDATE()) AND MONTH(Holiday)=MONTH(GETDATE()) 

AND Holiday>=@StartTime AND Holiday<=@EndTime

 SET @SurplusDay= [dbo].[GetWorkerDays](@StartTime,DATEADD(DAY,1,@EndTime))--剩餘工作日

 SELECT @CountDay=COUNT(0) FROM dbo.TimeSpanDays(@StartTime ,DATEADD(DAY,1,@EndTime))  --總共天數  計算出 時間段總共天數

 return @CountDay-@SurplusDay+@LegalRest-@AdjustmentDay

END

3、計算當前前月初日期和月末日期

月初

簡單：

SELECT  CONVERT(VARCHAR(7),GETDATE(),120)+'-01'

月末日期

這個也簡單簡單：就是月第一天加一月再減去一天

SELECT   DATEADD(DAY,-1, DATEADD(MONTH,1, CONVERT(VARCHAR(7),GETDATE(),120)+'-01'))