sql server 生成全年日历

以下是使用 SQL Server 生成全年日历的示例查询：

DECLARE @year INT = 2021;

WITH all_dates AS (
    SELECT DATEFROMPARTS(@year, 1, 1) AS date
    UNION ALL
    SELECT DATEADD(day, 1, date) AS date
    FROM all_dates
    WHERE DATEPART(year, date) = @year
)

SELECT 
    DATEPART(ISO_WEEK, date) AS week_number,
    DATEPART(DAY, date) AS day_of_month,
    DATENAME(WEEKDAY, date) AS day_of_week,
    DATENAME(MONTH, date) AS month_name,
    DATEPART(MONTH, date) AS month_number,
    DATEPART(YEAR, date) AS year_number
FROM all_dates
WHERE DATEPART(YEAR, date) = @year
OPTION (MAXRECURSION 366);

该查询使用递归公用表表达式生成全年的日期，并使用 DATEPART 和 DATENAME 函数从日期中提取所需的信息。运行该查询将生成从 1 月 1 日到 12 月 31 日的所有日期，以及每个日期的星期数、月份名称和编号等信息。