SqlServer将月拆分成周,包括 周起始日期 和 周结束日期,周度不跨月,默认1号是第一周起始日,如果1号是周日,那么第一周只有1天;同理,当月最后一天是最后一周的结束日,具体看运行结果;
/*
SET DATEFIRST 1;
-- 本月所有的天数
WITH T AS
(
SELECT CONVERT(VARCHAR(10),DATEADD(DAY,T2.NUMBER,T1.DAY),120) AS DAY FROM
(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),120),1,7)+'-01' DAY) T1,
(SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER>=0 AND NUMBER<=31) T2
WHERE CONVERT(VARCHAR(10),DATEADD(DAY,T2.NUMBER,T1.DAY),120) LIKE SUBSTRING(CONVERT(VARCHAR,GETDATE(),120),1,7)+'%'
),
T2 AS
(
SELECT T1.DAY,CAST(DATEPART(MM,T1.DAY) AS VARCHAR(2)) AS MONTH , '第'+ CAST((DATEPART(WK,T1.DAY) - DATEPART(WK,CONVERT(VARCHAR(7),T1.DAY,120) + '-01') + 1) AS VARCHAR(2)) + '周' AS 周
FROM T T1
),
T3 AS
(
SELECT T2.DAY,CASE WHEN 周='第1周' THEN '第一周'
WHEN 周='第2周' THEN '第二周'
WHEN 周='第3周' THEN '第三周'
WHEN 周='第4周' THEN '第四周'
WHEN 周='第5周' THEN '第五周'
WHEN 周='第6周' THEN '第六周'
ELSE NULL END AS 周
FROM T2
),
T4 AS
(
SELECT MIN(DAY) AS WEEK_BEGIN,MAX(DAY) AS WEEK_COMMIT,周 FROM T3
GROUP BY 周
)
SELECT * INTO #T4 FROM T4;
SELECT * FROM #T4
--DROP TABLE #T4;
*/
结果如下:
上面只计算当月的,如果计算其他月份的,自己改一下;