--@StartDate 本年度第一天
--@EndDate 本年度最后一天
DECLARE @StartDate DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
DECLARE @EndDate DATETIME = DATEADD(ms, -3,
DATEADD(yy,
DATEDIFF(yy, 0, GETDATE()) + 1,
0))
IF EXISTS ( SELECT *
FROM tempdb..sysobjects
WHERE id = OBJECT_ID('tempdb..#tb_work') )
BEGIN
DROP TABLE #tb_work
END
CREATE TABLE #tb_work
(
RN INT ,
WorkDate DATE ,--日期
DeWeek NVARCHAR(10) ,--星期几
IsWork NVARCHAR(10) --是否上班
)
INSERT INTO #tb_work
( RN ,
WorkDate ,
DeWeek
)
SELECT ROW_NUMBER() OVER ( ORDER BY CAST(DATEADD(day, number,
@StartDate) AS DATE) ) ,
CAST(DATEADD(day, number, @StartDate) AS DATE) AS dt ,
DATENAME(WEEKDAY,
CAST(DATEADD(day, number, @StartDate) AS DATE))
FROM master.dbo.spt_values
WHERE type = 'P'
AND number <= DATEDIFF(day, @StartDate, @EndDate)
SELECT * FROM #tb_work;
image
下面根据自己数据库配置的情况更新表中的“是否上班”的字段
例如我们有个配置表如下：
image
我们先把配置表里的节假日信息数据填充到临时表
UPDATE tw WITH(ROWLOCK)
SET tw.IsWork = TB.IsShipment
FROM #tb_work tw WITH ( ROWLOCK )
INNER JOIN EDI_BaseHoliday tb ON Tw.WorkDate = tb.Holiday</pre>
image
由于我们公司除配置表配置的节假日外，周末是休息的，那么将临时表中没更新“是否工作日”的字段进行update
UPDATE #tb_work
SET IsWork = ( CASE WHEN DeWeek = '星期六'
OR DeWeek = '星期日' THEN 'N'
ELSE 'Y'
END )
WHERE ISNULL(IsWork, '') = '' </pre>
这样本年度的节假日信息就出来了
