SQL Server 获取2019年节假日列表（可用于Java、.Net系统实现）

考勤统计中，一般都需要考虑到节假日信息和调休的日期，每年12月份国家会颁布新一年的节假日信息，我们可根据节假日信息，维护成相关的日期表，用于日期维护。本文介绍下相关实现方式。 

日期表的创建如下：

CREATE TABLE [dbo].[HR_WorkDayList](
	[WorkDate] [varchar](10) NOT NULL,
	[DateType] [nvarchar](10) NULL,
	[IsWorkDay] [bit] NULL,
	[Remark] [nvarchar](20) NULL,
 CONSTRAINT [PK_HR_WorkDayList] PRIMARY KEY CLUSTERED 
(
	[WorkDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

可根据系统表插入每年的日期列表，SQL脚本如下（可以一次性生成几年内的日期列表，在系统设计中，我这里生成了2018年-2028年的日期数据）：

INSERT INTO [dbo].[HR_WorkDayList](WorkDate,DateType,IsWorkDay,Remark) select WorkDate,DateType,IsWorkDay,Remark from 

(SELECT convert(char(10), DATEADD(dd, number, '2023-08-11'),120) AS WorkDate, 
datename(weekday,convert(char(10), DATEADD(dd, number, 2023-08-11),120)) as DateType, 
case when datename(weekday,convert(char(10), DATEADD(dd, number, 2023-08-11),120))<>'星期六' and datename(weekday,convert(char(10), DATEADD(dd, number, 2023-08-11),120))<>'星期日' then 1 else 0 end as IsWorkDay,
'' as Remark
FROM   master.dbo.spt_values  as spt
WHERE   type = 'p' AND number <= DATEDIFF(DAY, '2023-08-11', '2028-12-31')) as SourceTable

根据每年国家发布的节假日信息，更新相关日期。采用Excel表格，组合成SQL脚本，然后更新表数据 。下表是我整理的2019年的节假日信息。 

日期		备注	是否上班	SQL脚本更新
2018-12-29		上班	1	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2018-12-29'
2018-12-30		元旦	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='元旦'WHERE WorkDate='2018-12-30'
2018-12-31		元旦	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='元旦'WHERE WorkDate='2018-12-31'
2019-01-01		元旦	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='元旦'WHERE WorkDate='2019-01-01'
2019-02-03		上班	1	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2019-02-03'
2019-02-03		上班	1	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2019-02-03'
2019-02-04		春节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春节'WHERE WorkDate='2019-02-04'
2019-02-05		春节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春节'WHERE WorkDate='2019-02-05'
2019-02-06		春节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春节'WHERE WorkDate='2019-02-06'
2019-02-07		春节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春节'WHERE WorkDate='2019-02-07'
2019-02-08		春节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春节'WHERE WorkDate='2019-02-08'
2019-02-09		春节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春节'WHERE WorkDate='2019-02-09'
2019-02-10		春节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春节'WHERE WorkDate='2019-02-10'
2019-04-05		清明节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='清明节'WHERE WorkDate='2019-04-05'
2019-04-06		清明节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='清明节'WHERE WorkDate='2019-04-06'
2019-04-07		清明节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='清明节'WHERE WorkDate='2019-04-07'
2019-05-01		劳动节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='劳动节'WHERE WorkDate='2019-05-01'
2019-06-07		端午节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='端午节'WHERE WorkDate='2019-06-07'
2019-06-08		端午节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='端午节'WHERE WorkDate='2019-06-08'
2019-06-09		端午节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='端午节'WHERE WorkDate='2019-06-09'
2019-09-13		中秋节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='中秋节'WHERE WorkDate='2019-09-13'
2019-09-14		中秋节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='中秋节'WHERE WorkDate='2019-09-14'
2019-09-15		中秋节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='中秋节'WHERE WorkDate='2019-09-15'
2019-09-29		上班	1	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2019-09-29'
2019-10-12		上班	1	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2019-10-12'
2019-10-01		国庆节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='国庆节'WHERE WorkDate='2019-10-01'
2019-10-02		国庆节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='国庆节'WHERE WorkDate='2019-10-02'
2019-10-03		国庆节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='国庆节'WHERE WorkDate='2019-10-03'
2019-10-04		国庆节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='国庆节'WHERE WorkDate='2019-10-04'
2019-10-05		国庆节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='国庆节'WHERE WorkDate='2019-10-05'
2019-10-06		国庆节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='国庆节'WHERE WorkDate='2019-10-06'
2019-10-07		国庆节	0	UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='国庆节'WHERE WorkDate='2019-10-07'