By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @SDATE DATE
DECLARE @EDATE DATE
SET @SDATE = '2020-01-01'
SET @EDATE = '2020-12-31'
-- 產生該月份所有的日期
;WITH DATE_CTE AS
(
SELECT @SDATE AS D
UNION ALL
SELECT DATEADD(DAY, 1, D)
FROM DATE_CTE
WHERE D <= @EDATE
)
-- 對日期加上月份、週次、星期
, INFO_CTE AS
(
SELECT DATEPART(MONTH, D) AS M,
DATEPART(WEEK, D) AS W,
DATEPART(WEEKDAY, D) AS WD,
DATEPART(DAY, D) AS D
FROM DATE_CTE
)
-- 將資料轉成日曆格式
, PIVOT_CTE AS
(
SELECT [M] AS '月份',
[1] AS 星期日,
[2] AS 星期一,
[3] AS 星期二,
[4] AS 星期三,
[5] AS 星期四,
[6] AS 星期五,
[7] AS 星期六
FROM INFO_CTE AS T
PIVOT (
| 月份 | 星期日 | 星期一 | 星期二 | 星期三 | 星期四 | 星期五 | 星期六 |
|---|---|---|---|---|---|---|---|
| 1 | null | null | null | 1 | 2 | 3 | 4 |
| 1 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 1 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 1 | 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 1 | 26 | 27 | 28 | 29 | 30 | 31 | null |
| 2 | null | null | null | null | null | null | 1 |
| 2 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| 2 | 9 | 10 | 11 | 12 | 13 | 14 | 15 |
| 2 | 16 | 17 | 18 | 19 | 20 | 21 | 22 |
| 2 | 23 | 24 | 25 | 26 | 27 | 28 | 29 |
| 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 3 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 3 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 3 | 22 | 23 | 24 | 25 | 26 | 27 | 28 |
| 3 | 29 | 30 | 31 | null | null | null | null |
| 4 | null | null | null | 1 | 2 | 3 | 4 |
| 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 4 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 4 | 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 4 | 26 | 27 | 28 | 29 | 30 | null | null |
| 5 | null | null | null | null | null | 1 | 2 |
| 5 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 5 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 5 | 17 | 18 | 19 | 20 | 21 | 22 | 23 |
| 5 | 24 | 25 | 26 | 27 | 28 | 29 | 30 |
| 5 | 31 | null | null | null | null | null | null |
| 6 | null | 1 | 2 | 3 | 4 | 5 | 6 |
| 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 |
| 6 | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| 6 | 21 | 22 | 23 | 24 | 25 | 26 | 27 |
| 6 | 28 | 29 | 30 | null | null | null | null |
| 7 | null | null | null | 1 | 2 | 3 | 4 |
| 7 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 7 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 7 | 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 7 | 26 | 27 | 28 | 29 | 30 | 31 | null |
| 8 | null | null | null | null | null | null | 1 |
| 8 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 |
| 8 | 16 | 17 | 18 | 19 | 20 | 21 | 22 |
| 8 | 23 | 24 | 25 | 26 | 27 | 28 | 29 |
| 8 | 30 | 31 | null | null | null | null | null |
| 9 | null | null | 1 | 2 | 3 | 4 | 5 |
| 9 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 9 | 13 | 14 | 15 | 16 | 17 | 18 | 19 |
| 9 | 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| 9 | 27 | 28 | 29 | 30 | null | null | null |
| 10 | null | null | null | null | 1 | 2 | 3 |
| 10 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 |
| 10 | 18 | 19 | 20 | 21 | 22 | 23 | 24 |
| 10 | 25 | 26 | 27 | 28 | 29 | 30 | 31 |
| 11 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 11 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 11 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 11 | 22 | 23 | 24 | 25 | 26 | 27 | 28 |
| 11 | 29 | 30 | null | null | null | null | null |
| 12 | null | null | 1 | 2 | 3 | 4 | 5 |
| 12 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 |
| 12 | 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| 12 | 27 | 28 | 29 | 30 | 31 | null | null |