<span>2019年日历假期添加</span>

考勤统计中，一般都需要考虑到节假日信息和调休的日期，每年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′