原创文章SQL生成每年的日期列表(年度日历表)显示周名
![](http://www.csframework.com/images/article-type3.gif)
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
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
![贴图图片](http://www.csframework.com/upload/image/微信截图_20181207185404.png)
版权声明:本文为开发框架文库发布内容,转载请附上原文出处连接
NewDoc C/S框架网