原创文章SQL生成每年的日期列表(年度日历表)显示周名

ＳＱＬ Code:

ALTER PROCEDURE usp_sys_genYearWorkDays
@Year INT --年度编码
AS
BEGIN

/******************************************************
程序说明：SQL生成每年的日期列表(年度日历表)显示周名
作者：C/S框架网 www.csframework.com

usp_sys_genYearWorkDays 2017
usp_sys_genYearWorkDays 2018

*******************************************************/

DECLARE @BeginDate DATETIME,@EndDate DATETIME
SELECT @BeginDate=CAST(@Year AS VARCHAR)+'-01-01'; --计算年度起始日期
SELECT @EndDate=CAST(CAST(@Year+1 AS VARCHAR)+'-01-01' AS DATETIME)-1--计算年度结束日期

--当年的总天数
DECLARE @YearCount FLOAT
SELECT @YearCount=DATEPART(DAYOFYEAR,@EndDate)

--偏移天数,用于spt_values表运算
DECLARE @Count FLOAT
SELECT @Count=ABS(DATEDIFF(d,GETDATE(),@BeginDate))

---SELECT @YearCount,@Count,@BeginDate,@EndDate

CREATE TABLE #Result
(
isid INT IDENTITY(1,1),
WorkDay DATETIME ,
IsLegalHoliday INT DEFAULT(0),
WeekName NVARCHAR(20)
)

INSERT INTO #Result(WorkDay)
SELECT CONVERT (VARCHAR (100),DATEADD(d, number, GETDATE() - @Count),23) AS every_time
FROM master..spt_values n
WHERE n.type = 'p' AND n.number < @YearCount;

--更新周名
UPDATE #Result SET WeekName=DATENAME(WEEKDAY, WorkDay)

--是否周末
UPDATE #Result SET IsLegalHoliday=1 WHERE WeekName IN ('星期六','星期日')

--返回结果
SELECT * FROM #Result
END;

//来源:C/S框架网(www.csframework.com) QQ:23404761