如何在T-SQL中实现波斯(Shamsi)日期与公历的转换？求高效SQL方案

嘿，我来分享两个经过验证的纯T-SQL解决方案，正好对应你的两个需求——不管是波斯转公历，还是批量把公历转成波斯日期，都能高效搞定，完全在数据库层面处理，比外部转换快得多。

如果你手里的波斯日期是yyyy/mm/dd这样的字符串，或者已经拆分成年、月、日的数值，可以用下面这个标量函数来转换。它基于标准的波斯历与公历转换算法，准确性可靠，而且纯SQL实现，不需要任何外部依赖：

CREATE FUNCTION dbo.ShamsiToGregorian
(
    @ShamsiYear INT,
    @ShamsiMonth INT,
    @ShamsiDay INT
)
RETURNS DATE
AS
BEGIN
    DECLARE @gy INT, @gm INT, @gd INT;
    DECLARE @jy INT = @ShamsiYear, @jm INT = @ShamsiMonth, @jd INT = @ShamsiDay;

    -- 定义波斯历各月份的天数（考虑闰年）
    DECLARE @daysInMonthJY TABLE (Month INT, Days INT);
    INSERT INTO @daysInMonthJY VALUES
        (1, 31), (2, 31), (3, 31), (4, 31), (5, 31), (6, 31),
        (7, 30), (8, 30), (9, 30), (10, 30), (11, 30), (12, 29 + CASE WHEN (@jy % 33) IN (1, 5, 9, 13, 17, 22, 26, 30) THEN 1 ELSE 0 END);

    -- 计算该日期在波斯历中的累计天数
    DECLARE @totalDays INT = @jd;
    SELECT @totalDays = @totalDays + Days FROM @daysInMonthJY WHERE Month < @jm;

    -- 转换为公历年份基准
    DECLARE @jy1 INT = @jy - 474;
    DECLARE @cycle INT = @jy1 / 2820;
    DECLARE @remaining INT = @jy1 % 2820;
    DECLARE @gy1 INT;

    IF @remaining <= 68
        SET @gy1 = 2820 * @cycle + @remaining + 474;
    ELSE
        SET @gy1 = 2820 * @cycle + @remaining + 473;

    -- 计算对应公历日期
    DECLARE @daysSinceMarch21 INT = @totalDays - 1;
    DECLARE @march21Gregorian DATE = DATEFROMPARTS(@gy1, 3, 21);
    DECLARE @gregorianDate DATE = DATEADD(DAY, @daysSinceMarch21, @march21Gregorian);

    SET @gy = YEAR(@gregorianDate);
    SET @gm = MONTH(@gregorianDate);
    SET @gd = DAY(@gregorianDate);

    RETURN DATEFROMPARTS(@gy, @gm, @gd);
END
GO

使用示例： #

如果你有一个波斯日期字符串1402/05/15，可以拆分后调用函数：

SELECT dbo.ShamsiToGregorian(1402, 5, 15) AS GregorianDate;

或者如果你的字段是字符串类型，可以先拆分后转换：

SELECT 
    dbo.ShamsiToGregorian(
        CAST(SUBSTRING(ShamsiDateColumn, 1, 4) AS INT),
        CAST(SUBSTRING(ShamsiDateColumn, 6, 2) AS INT),
        CAST(SUBSTRING(ShamsiDateColumn, 9, 2) AS INT)
    ) AS GregorianDate
FROM YourTable;

对于大量数据的报表场景，我更推荐用内联表值函数（ITVF），它的性能比标量函数好很多，因为SQL Server优化器会把它和主查询合并执行，避免逐行调用的开销：

CREATE FUNCTION dbo.GregorianToShamsi
(
    @GregorianDate DATE
)
RETURNS TABLE
AS
RETURN
(
    WITH GregorianInfo AS (
        SELECT
            YEAR(@GregorianDate) AS gy,
            MONTH(@GregorianDate) AS gm,
            DAY(@GregorianDate) AS gd,
            DATEDIFF(DAY, '0001-01-01', @GregorianDate) + 1 AS julianDay
    ),
    JulianConversion AS (
        SELECT
            gy, gm, gd, julianDay,
            julianDay - DATEDIFF(DAY, '0001-03-21', DATEFROMPARTS(gy, 3, 21)) + 1 AS dayOfYear,
            CASE WHEN gm > 2 THEN gy ELSE gy - 1 END AS gyForMarch
        FROM GregorianInfo
    ),
    ShamsiCalculation AS (
        SELECT
            gy, gm, gd, julianDay, dayOfYear,
            474 + ((gyForMarch - 474) * 365 + (gyForMarch - 474) / 4 - (gyForMarch - 474) / 100 + (gyForMarch - 474) / 400 - DATEDIFF(DAY, '0001-03-21', '0001-01-01')) / 365.2425 AS jyFloat
        FROM JulianConversion
    ),
    ShamsiYear AS (
        SELECT
            gy, gm, gd, julianDay, dayOfYear,
            FLOOR(jyFloat) AS jy,
            jyFloat - FLOOR(jyFloat) AS jyFraction
        FROM ShamsiCalculation
    ),
    ShamsiStart AS (
        SELECT
            gy, gm, gd, julianDay, dayOfYear, jy,
            DATEADD(DAY, FLOOR(jyFraction * 365.2425), DATEFROMPARTS(474, 3, 21)) AS shamsiYearStart
        FROM ShamsiYear
    ),
    DayOfShamsiYear AS (
        SELECT
            gy, gm, gd, julianDay, jy,
            DATEDIFF(DAY, shamsiYearStart, @GregorianDate) + 1 AS doy
        FROM ShamsiStart
    ),
    ShamsiMonth AS (
        SELECT
            jy, doy,
            CASE
                WHEN doy <= 186 THEN CEILING(doy / 31.0)
                ELSE CEILING((doy - 186) / 30.0) + 6
            END AS jm,
            CASE
                WHEN doy <= 186 THEN doy - (CEILING(doy / 31.0) - 1) * 31
                ELSE doy - 186 - (CEILING((doy - 186) / 30.0) - 1) * 30
            END AS jd
        FROM DayOfShamsiYear
    )
    SELECT
        jy AS ShamsiYear,
        jm AS ShamsiMonth,
        jd AS ShamsiDay,
        CONCAT(jy, '/', FORMAT(jm, '00'), '/', FORMAT(jd, '00')) AS ShamsiDateString
    FROM ShamsiMonth
)
GO

使用示例（批量处理）： #

直接在查询中调用这个表值函数，处理整个表的日期字段，性能非常适合大数据量：

SELECT
    t.YourId,
    t.GregorianDateColumn,
    s.ShamsiYear,
    s.ShamsiMonth,
    s.ShamsiDay,
    s.ShamsiDateString
FROM YourTable t
CROSS APPLY dbo.GregorianToShamsi(t.GregorianDateColumn) s;

性能小贴士： #

• 内联表值函数会被SQL Server优化器展开，和查询本身一起执行，避免了标量函数的逐行调用开销，处理百万级数据时优势明显。
• 如果你的日期字段经常需要转换，可以考虑添加持久化计算列，把波斯日期预计算并存储，报表查询时直接读取即可：

  ALTER TABLE YourTable
  ADD ShamsiDate AS (SELECT ShamsiDateString FROM dbo.GregorianToShamsi(GregorianDateColumn)) PERSISTED;
  

  上面的函数是确定性的，完全符合持久化计算列的要求。

内容的提问来源于stack exchange，提问作者Difender Amini