SQL Server自动产生一年多少周及月

工作中常需要产生某一年的周数、月份，比如按周排计划、按月核算工资……

1、自动产生周数（含每周起始及结束日期）

DECLARE @FirstDateOfYear DATETIME
SET @FirstDateOfYear='2017-01-01';
WITH T AS
(
    SELECT DATEADD(D,NUMBER,@FirstDateOfYear) D
    FROM MASTER.DBO.SPT_VALUES
    WHERE [TYPE]='P' AND NUMBER<DATEDIFF(D,@FirstDateOfYear,DATEADD(YY,1,@FirstDateOfYear))
)
SELECT DATEPART(YY,D) '年份',DATEPART(WEEK,D) '周数',CONVERT(VARCHAR,MIN(D),120) '起始日期',CONVERT(VARCHAR,MAX(D),120) '结束日期'
FROM T
GROUP BY DATEPART(YY,D),DATEPART(WEEK,D)

执行结果：

2、自动产生月份（含每月起始及结束日期）

DECLARE @FirstDateOfYear DATETIME
SET @FirstDateOfYear='2017-01-01';
WITH T AS
(
    SELECT DATEADD(M,NUMBER,@FirstDateOfYear) D
    FROM MASTER.DBO.SPT_VALUES
    WHERE [TYPE]='P' AND NUMBER BETWEEN 0 AND 11
)
SELECT DATEPART(YY,D) '年份',DATEPART(MM,D) '月份',CONVERT(VARCHAR(10),D,120) '起始日期',
    CONVERT(VARCHAR(10),DATEADD(Y,-1,DATEADD(M,1,D)),120) '结束日期'
FROM T