SQL Server 自訂函數實作：依年月生成文字版月曆與表格版月曆兩種輸出模式

函數一：文字版月曆 fn_Calendar (回傳 NVARCHAR(MAX))

CREATE FUNCTION fn_Calendar(@year INT, @month INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX), @Enter NVARCHAR(8);
    SELECT @Enter = CHAR(13) + CHAR(10),
           @result = ' Sun Mon The Wed Thu Fri Sta' + @Enter;  -- 表頭

    DECLARE @start DATETIME, @end DATETIME;
    SELECT @start = CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-1',
           @end = DATEADD(MONTH, 1, @start);

    -- 第一行前置空白 (對齊星期)
    SET @result = @result + REPLICATE('    ', (DATEPART(WEEKDAY, @start) + @@DATEFIRST + 6) % 7);

    WHILE DATEDIFF(DAY, @start, @end) > 0
    BEGIN
        -- 每週日換行
        IF (DATEPART(WEEKDAY, @start) + @@DATEFIRST) % 7 = 1
            SELECT @result = @result + @Enter;

        SELECT @result = @result + RIGHT('   ' + RTRIM(DAY(@start)), 4),
               @start = DATEADD(DAY, 1, @start);
    END

    RETURN @result;
END;
GO

-- 測試
SET DATEFIRST 3;  -- 設定週三為每週第一天 (可依需求調整)
PRINT dbo.fn_Calendar(2007, 12);
SET DATEFIRST 7;  -- 還原預設 (週日)
DROP FUNCTION dbo.fn_Calendar;

輸出範例 (DATEFIRST=3)

 Sun Mon The Wed Thu Fri Sta
                        1
   2   3   4   5   6   7   8
   9  10  11  12  13  14  15
  16  17  18  19  20  21  22
  23  24  25  26  27  28  29
  30  31

函數二：表格版月曆 f_calendar (回傳 TABLE)

CREATE FUNCTION f_calendar(@year INT, @month INT)
RETURNS @t TABLE (
    [日] VARCHAR(4), [一] VARCHAR(4), [二] VARCHAR(4), [三] VARCHAR(4),
    [四] VARCHAR(4), [五] VARCHAR(4), [六] VARCHAR(4)
)
AS
BEGIN
    DECLARE @a TABLE (id INT IDENTITY(0,1), dt DATETIME);

    -- 產生 1~31 號日期序列
    INSERT INTO @a(dt)
    SELECT TOP 31 CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-1' FROM sysobjects;

    UPDATE @a SET dt = DATEADD(DAY, id, dt);

    -- 樞紐轉置：依週分組，各星期欄位填入日期
    INSERT INTO @t
    SELECT
        MAX(CASE WHEN DATEPART(WEEKDAY, dt) = 7 THEN RTRIM(DAY(dt)) ELSE '' END),
        MAX(CASE WHEN DATEPART(WEEKDAY, dt) = 1 THEN RTRIM(DAY(dt)) ELSE '' END),
        MAX(CASE WHEN DATEPART(WEEKDAY, dt) = 2 THEN RTRIM(DAY(dt)) ELSE '' END),
        MAX(CASE WHEN DATEPART(WEEKDAY, dt) = 3 THEN RTRIM(DAY(dt)) ELSE '' END),
        MAX(CASE WHEN DATEPART(WEEKDAY, dt) = 4 THEN RTRIM(DAY(dt)) ELSE '' END),
        MAX(CASE WHEN DATEPART(WEEKDAY, dt) = 5 THEN RTRIM(DAY(dt)) ELSE '' END),
        MAX(CASE WHEN DATEPART(WEEKDAY, dt) = 6 THEN RTRIM(DAY(dt)) ELSE '' END)
    FROM @a
    WHERE MONTH(dt) = @month
    GROUP BY CASE WHEN DATEPART(WEEKDAY, dt) = 7 THEN DATEPART(WEEK, dt) + 1 ELSE DATEPART(WEEK, dt) END;

    RETURN;
END;
GO

-- 測試
SET DATEFIRST 1;  -- 週一為每週第一天
SELECT * FROM dbo.f_calendar(2007, 12);
DROP FUNCTION f_calendar;

輸出範例 (DATEFIRST=1)

日   一   二   三   四   五  六
---- ---- ---- ---- ---- ---- ----
             1
2    3    4    5    6    7    8
9   10   11   12   13   14   15
16  17   18   19   20   21   22
23  24   25   26   27   28   29
30  31

兩版本比較與選用建議

建議：文字版 適合快速產生、無視 DATEFIRST 設定；表格版 適合結構化資料消費、報表整合。