SELECT ZR.NAME
,HLI.HOLIDAY_NAME
,HD.START_DATETIME
,HD.END_DATETIME
,DATEDIFF(DAY,HD.START_DATETIME,HD.END_DATETIME) DAYS
FROM HOLIDAYZONEMM HZM
LEFT JOIN ZONERULE ZR ON HZM.HOLIDAYZONEID = ZR.ZONERULEID
LEFT JOIN HOLIDAY HLI ON HZM.HOLIDAYID = HLI.HOLIDAYID
LEFT JOIN HOLIDAYDATE HD ON HZM.HOLIDAYID = HD.HOLIDAYID
WHERE ZR.TYPE = N'H'
ORDER BY ZR.NAME,HD.START_DATETIME
获取法定节假日的具体日期
DECLARE @AllHoliday TABLE
(
NAME nvarchar(200) NOT NULL
,HOLIDAY_NAME nvarchar(200) NOT NULL
,START_DATETIME DATETIME NOT NULL
,END_DATETIME DATETIME NOT NULL
,DAYS INT NOT NULL
)
INSERT INTO @AllHoliday
SELECT ZR.NAME
,HLI.HOLIDAY_NAME
,HD.START_DATETIME
,HD.END_DATETIME
,DATEDIFF(DAY,HD.START_DATETIME,HD.END_DATETIME) DAYS
FROM HOLIDAYZONEMM HZM
LEFT JOIN ZONERULE ZR ON HZM.HOLIDAYZONEID = ZR.ZONERULEID
LEFT JOIN HOLIDAY HLI ON HZM.HOLIDAYID = HLI.HOLIDAYID
LEFT JOIN HOLIDAYDATE HD ON HZM.HOLIDAYID = HD.HOLIDAYID
WHERE ZR.TYPE = N'H'
ORDER BY ZR.NAME,HD.START_DATETIME
declare @holiday nvarchar(200)
declare @begindate date,@enddate date
declare @t table(NAME nvarchar(200), EFFDT date)
Declare hCursor Cursor Local
For Select HOLIDAY_NAME,START_DATETIME,END_DATETIME From @AllHoliday Where NAME=N'中国假期'
Open hCursor
Fetch next From hCursor Into @holiday,@begindate,@enddate
While(@@Fetch_Status = 0)
BEGIN
while (@begindate<@enddate)
begin
insert into @t select @holiday, @begindate
select @begindate= DATEADD(DAY,1,@begindate)
end
Fetch next From hCursor Into @holiday,@begindate,@enddate
END
Close hCursor
Deallocate hCursor