---------------------------------*/
-->1.创建法定节假日表
IF NOT OBJECT_ID('[法定节假日表]') IS NULL
DROP TABLE [法定节假日表]
GO
CREATE TABLE [法定节假日表]([YearID] INT,[DATE] DATETIME,[ISWORKDATE] BIT,[NOTE] NVARCHAR(150))
INSERT [法定节假日表]
SELECT 2009,N'2009-01-01',0,N'元旦:1月1至3日放假3天,1月4日(星期日)上班' UNION ALL
SELECT 2009,N'2009-01-02',0,N'元旦:1月1至3日放假3天,1月4日(星期日)上班' UNION ALL
SELECT 2009,N'2009-01-03',0,N'元旦:1月1至3日放假3天,1月4日(星期日)上班' UNION ALL
SELECT 2009,N'2009-01-04',1,NULL UNION ALL
SELECT 2009,N'2009-01-24',1,NULL UNION ALL
SELECT 2009,N'2009-01-25',0,N'春节:1月25日至31日放假7天,1月24日(星期六)、2月1日(星期日)上班' UNION ALL
SELECT 2009,N'2009-01-26',0,N'春节:1月25日至31日放假7天,1月24日(星期六)、2月1日(星期日)上班' UNION ALL
SELECT 2009,N'2009-01-27',0,N'春节:1月25日至31日放假7天,1月24日(星期六)、2月1日(星期日)上班' UNION ALL
SELECT 2009,N'2009-01-28',0,N'春节:1月25日至31日放假7天,1月24日(星期六)、2月1日(星期日)上班' UNION ALL
SELECT 2009,N'2009-01-29',0,N'春节:1月25日至31日放假7天,1月24日(星期六)、2月1日(星期日)上班' UNION ALL
SELECT 2009,N'2009-01-30',0,N'春节:1月25日至31日放假7天,1月24日(星期六)、2月1日(星期日)上班' UNION ALL
SELECT 2009,N'2009-01-31',0,N'春节:1月25日至31日放假7天,1月24日(星期六)、2月1日(星期日)上班' UNION ALL
SELECT 2009,N'2009-02-01',1,NULL UNION ALL
SELECT 2009,N'2009-04-04',0,N'清明节:4月4日至6日放假3天' UNION ALL
SELECT 2009,N'2009-04-05',0,N'清明节:4月4日至6日放假3天' UNION ALL
SELECT 2009,N'2009-04-06',0,N'清明节:4月4日至6日放假3天' UNION ALL
SELECT 2009,N'2009-05-01',0,N'劳动节:5月1日至3日放假3天' UNION ALL
SELECT 2009,N'2009-05-02',0,N'劳动节:5月1日至3日放假3天' UNION ALL
SELECT 2009,N'2009-05-03',0,N'劳动节:5月1日至3日放假3天' UNION ALL
SELECT 2009,N'2009-05-28',0,N'端午节:5月28日至30日放假3天,5月31日(星期日)上班' UNION ALL
SELECT 2009,N'2009-05-29',0,N'端午节:5月28日至30日放假3天,5月31日(星期日)上班' UNION ALL
SELECT 2009,N'2009-05-30',0,N'端午节:5月28日至30日放假3天,5月31日(星期日)上班' UNION ALL
SELECT 2009,N'2009-05-31',1,NULL UNION ALL
SELECT 2009,N'2009-09-27',1,NULL UNION ALL
SELECT 2009,N'2009-10-01',0,N'国庆节、中秋节:10月1日至8日放假8天,9月27日(星期日)、10月9日(星期五)、10月10日(星期六)上班' UNION ALL
SELECT 2009,N'2009-10-02',0,N'国庆节、中秋节:10月1日至8日放假8天,9月27日(星期日)、10月9日(星期五)、10月10日(星期六)上班' UNION ALL
SELECT 2009,N'2009-10-03',0,N'国庆节、中秋节:10月1日至8日放假8天,9月27日(星期日)、10月9日(星期五)、10月10日(星期六)上班' UNION ALL
SELECT 2009,N'2009-10-04',0,N'国庆节、中秋节:10月1日至8日放假8天,9月27日(星期日)、10月9日(星期五)、10月10日(星期六)上班' UNION ALL
SELECT 2009,N'2009-10-05',0,N'国庆节、中秋节:10月1日至8日放假8天,9月27日(星期日)、10月9日(星期五)、10月10日(星期六)上班' UNION ALL
SELECT 2009,N'2009-10-06',0,N'国庆节、中秋节:10月1日至8日放假8天,9月27日(星期日)、10月9日(星期五)、10月10日(星期六)上班' UNION ALL
SELECT 2009,N'2009-10-07',0,N'国庆节、中秋节:10月1日至8日放假8天,9月27日(星期日)、10月9日(星期五)、10月10日(星期六)上班' UNION ALL
SELECT 2009,N'2009-10-08',0,N'国庆节、中秋节:10月1日至8日放假8天,9月27日(星期日)、10月9日(星期五)、10月10日(星期六)上班' UNION ALL
SELECT 2009,N'2009-10-10',1,NULL UNION ALL
SELECT 2008,N'2008-01-01',0,N'2007年12月30日—2008年1月1日放假,共3天。其中,1月1日(星期二)为法定节假日,12月30日(星期日)为公休日,12月29日(星期六)公休日调至12月31日(星期一),12月29日(星期六)上班。' UNION ALL
SELECT 2008,N'2008-02-02',1,NULL UNION ALL
SELECT 2008,N'2008-02-03',1,NULL UNION ALL
SELECT 2008,N'2008-04-04',0,N'清明节:4月4日—6日放假,共3天。其中,4月4日(清明节)为法定节假日,4月5日(星期六)、4月6日(星期日)照常公休。' UNION ALL
SELECT 2008,N'2008-04-05',0,N'清明节:4月4日—6日放假,共3天。其中,4月4日(清明节)为法定节假日,4月5日(星期六)、4月6日(星期日)照常公休。' UNION ALL
SELECT 2008,N'2008-04-06',0,N'清明节:4月4日—6日放假,共3天。其中,4月4日(清明节)为法定节假日,4月5日(星期六)、4月6日(星期日)照常公休。' UNION ALL
SELECT 2008,N'2008-05-01',0,N'“五一”国际劳动节:5月1日—3日放假,共3天。其中,5月1日为法定节假日,5月3日(星期六)为公休日,5月4日(星期日)公休日调至5月2日(星期五),5月4日(星期日)上班。' UNION ALL
SELECT 2008,N'2008-05-02',0,N'“五一”国际劳动节:5月1日—3日放假,共3天。其中,5月1日为法定节假日,5月3日(星期六)为公休日,5月4日(星期日)公休日调至5月2日(星期五),5月4日(星期日)上班。' UNION ALL
SELECT 2008,N'2008-05-03',0,N'“五一”国际劳动节:5月1日—3日放假,共3天。其中,5月1日为法定节假日,5月3日(星期六)为公休日,5月4日(星期日)公休日调至5月2日(星期五),5月4日(星期日)上班。' UNION ALL
SELECT 2008,N'2008-05-04',1,NULL UNION ALL
SELECT 2008,N'2008-06-07',0,N'端午节:6月7日—9日放假,共3天。其中,6月7日(星期六)照常公休,6月8日(农历五月初五,端午节)为法定节假日,6月8日(星期日)公休日调至6月9日(星期一)。' UNION ALL
SELECT 2008,N'2008-06-08',0,N'端午节:6月7日—9日放假,共3天。其中,6月7日(星期六)照常公休,6月8日(农历五月初五,端午节)为法定节假日,6月8日(星期日)公休日调至6月9日(星期一)。' UNION ALL
SELECT 2008,N'2008-06-09',0,N'端午节:6月7日—9日放假,共3天。其中,6月7日(星期六)照常公休,6月8日(农历五月初五,端午节)为法定节假日,6月8日(星期日)公休日调至6月9日(星期一)。' UNION ALL
SELECT 2008,N'2008-09-13',0,N'中秋节:9月13日—15日放假,共3天。其中,9月13日(星期六)为公休日,9月14日(农历八月十五,中秋节)为法定节假日,9月14日(星期日)公休日调至9月15日(星期一)。' UNION ALL
SELECT 2008,N'2008-09-14',0,N'中秋节:9月13日—15日放假,共3天。其中,9月13日(星期六)为公休日,9月14日(农历八月十五,中秋节)为法定节假日,9月14日(星期日)公休日调至9月15日(星期一)。' UNION ALL
SELECT 2008,N'2008-09-15',0,N'中秋节:9月13日—15日放假,共3天。其中,9月13日(星期六)为公休日,9月14日(农历八月十五,中秋节)为法定节假日,9月14日(星期日)公休日调至9月15日(星期一)。' UNION ALL
SELECT 2008,N'2008-09-29',0,N'国庆节:9月29日—10月5日放假,共7天。其中,10月1日、2日、3日为法定节假日,9月27日(星期六)、9月28日(星期日)两个公休日调至9月29日(星期一)、30日(星期二),10月4日(星期六)、5日(星期日)照常公休。' UNION ALL
SELECT 2008,N'2008-09-30',0,N'国庆节:9月29日—10月5日放假,共7天。其中,10月1日、2日、3日为法定节假日,9月27日(星期六)、9月28日(星期日)两个公休日调至9月29日(星期一)、30日(星期二),10月4日(星期六)、5日(星期日)照常公休。' UNION ALL
SELECT 2008,N'2008-10-01',0,N'国庆节:9月29日—10月5日放假,共7天。其中,10月1日、2日、3日为法定节假日,9月27日(星期六)、9月28日(星期日)两个公休日调至9月29日(星期一)、30日(星期二),10月4日(星期六)、5日(星期日)照常公休。' UNION ALL
SELECT 2008,N'2008-10-02',0,N'国庆节:9月29日—10月5日放假,共7天。其中,10月1日、2日、3日为法定节假日,9月27日(星期六)、9月28日(星期日)两个公休日调至9月29日(星期一)、30日(星期二),10月4日(星期六)、5日(星期日)照常公休。' UNION ALL
SELECT 2008,N'2008-10-03',0,N'国庆节:9月29日—10月5日放假,共7天。其中,10月1日、2日、3日为法定节假日,9月27日(星期六)、9月28日(星期日)两个公休日调至9月29日(星期一)、30日(星期二),10月4日(星期六)、5日(星期日)照常公休。' UNION ALL
SELECT 2008,N'2008-10-04',0,N'国庆节:9月29日—10月5日放假,共7天。其中,10月1日、2日、3日为法定节假日,9月27日(星期六)、9月28日(星期日)两个公休日调至9月29日(星期一)、30日(星期二),10月4日(星期六)、5日(星期日)照常公休。' UNION ALL
SELECT 2008,N'2008-10-05',0,N'国庆节:9月29日—10月5日放假,共7天。其中,10月1日、2日、3日为法定节假日,9月27日(星期六)、9月28日(星期日)两个公休日调至9月29日(星期一)、30日(星期二),10月4日(星期六)、5日(星期日)照常公休。'
GO
-->2.创建获取工作日的函数:
IF NOT OBJECT_ID('[fn_getworkdate]') IS NULL
DROP FUNCTION [fn_getworkdate]
GO
CREATE FUNCTION fn_getworkdate(@DT SMALLDATETIME)
RETURNS DATETIME
AS
BEGIN
WHILE EXISTS(SELECT 1 FROM [法定节假日表] WHERE [DATE]=@DT AND [ISWORKDATE]=0)
OR (DATEPART(W,@DT+@@DATEFIRST-1) IN(6,7)
AND EXISTS(SELECT 1 FROM [法定节假日表] WHERE [DATE]=@DT AND [ISWORKDATE]<>1))
BEGIN
SET @DT=DATEADD(DD,-1,@DT)
END
RETURN @DT
END
GO
--3.开始将数据库中所有日期字段更新为非法定节假日与周末(节假日中调整的周末不更新)
--3.1.法定节假日表创建索引
CREATE INDEX idx_法定节假日表创建索引 ON 法定节假日表([DATE],[ISWORKDATE])
GO
--3.2.游标遍历所有表更新
DECLARE @s VARCHAR(8000)
DECLARE c CURSOR FOR
SELECT 'UPDATE ['+OBJECT_NAME(id)+'] SET ['+name+']=dbo.fn_getworkdate(['+name+'])
WHERE EXISTS(SELECT 1 FROM [法定节假日表] WHERE [DATE]=['+OBJECT_NAME(id)+'].['+name+'] AND [ISWORKDATE]=0)
OR (DATEPART(W,['+OBJECT_NAME(id)+'].['+name+']+@@DATEFIRST-1) IN(6,7)
AND EXISTS(SELECT 1 FROM [法定节假日表]
WHERE [DATE]=['+OBJECT_NAME(id)+'].['+name+'] AND [ISWORKDATE]<>1))'
FROM syscolumns
WHERE TYPE_NAME(xtype) IN('datetime','smalldatetime')
AND OBJECTPROPERTY(id, N'IsUserTable')=1
AND OBJECT_NAME(id) NOT IN('法定节假日表')
OPEN c
FETCH NEXT FROM c INTO @s
WHILE @@FETCH_STATUS=0
BEGIN
EXEC(@s)
FETCH NEXT FROM c INTO @s
END
CLOSE c
DEALLOCATE c