kronos获取假期

最新推荐文章于 2023-12-29 16:25:46 发布

原创最新推荐文章于 2023-12-29 16:25:46 发布 · 583 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#kronos #sql

KRONOS 专栏收录该内容

5 篇文章

订阅专栏

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