SQL Server中date2日期转换报错原因及波斯日期转DATE解决方法

问题回顾 #

执行以下SQL时，第二条SELECT抛出错误：

Conversion failed when converting date and/or time from character string.

代码：

DECLARE @date1 datetime, @date2 datetime

SET @date1='2024-09-22 00:52:06.000'
SELECT CAST(FORMAT(@date1, 'yyyy/MM/dd', 'fa') AS DATE) date1

SET @date2='2024-09-21 00:52:06.000'
SELECT CAST(FORMAT(@date2, 'yyyy/MM/dd', 'fa') AS DATE) date2

错误仅出现在@date2的处理中，需求是将公历datetime格式化为波斯历字符串后，转换回DATE类型（必须返回DATE，不能用NVARCHAR）。

1. 错误仅针对@date2的原因 #

核心问题是逻辑误解+解析规则冲突：

• FORMAT(@date, 'yyyy/MM/dd', 'fa')会将公历日期转换为波斯历的字符串表示，且返回的字符串包含波斯语数字（如۰、۱、۲），而非阿拉伯数字。SQL Server的CAST(... AS DATE)无法识别波斯数字，因此转换时会报错。
• 你观察到@date1转换成功是特殊情况：可能会话的DATEFORMAT设置导致字符串被误解析为某个合法公历日期（比如波斯数字被意外识别为其他字符），而@date2的字符串组合完全无法匹配解析规则，因此触发错误。

另外，即使返回的是阿拉伯数字的波斯历字符串（如1403/06/30），CAST也会将其当作公历日期解析——而1403年远早于SQL Server DATE类型支持的最小公历日期（1753年1月1日），同样会报错。

2. 正确实现方式（返回DATE类型） #

SQL Server的DATE类型仅存储公历日期，无法直接存储波斯历日期。以下是符合需求的解决方案：

方案1：保留DATE类型，展示时格式化

如果函数只需返回原公历日期的DATE类型，在输出时用FORMAT格式化为波斯历字符串即可：

CREATE FUNCTION dbo.GetGregorianDate(@inputDate DATETIME)
RETURNS DATE
AS
BEGIN
    -- 直接返回原日期的DATE类型（公历）
    RETURN CAST(@inputDate AS DATE)
END
GO

-- 调用函数并格式化为波斯历展示
SELECT FORMAT(dbo.GetGregorianDate('2024-09-21'), 'yyyy/MM/dd', 'fa') AS PersianFormattedDate;

方案2：波斯历字符串转公历DATE（反向转换）

如果需要将波斯历字符串转换为对应的公历DATE，可以使用PARSE函数指定文化解析，先替换波斯数字为阿拉伯数字：

DECLARE @date2 DATETIME = '2024-09-21 00:52:06.000'
-- 获取波斯历字符串（含波斯数字）
DECLARE @persianStr NVARCHAR(20) = FORMAT(@date2, 'yyyy/MM/dd', 'fa')
-- 替换波斯数字为阿拉伯数字
SET @persianStr = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    @persianStr, N'۰', N'0'), N'۱', N'1'), N'۲', N'2'), N'۳', N'3'), N'۴', N'4'),
    N'۵', N'5'), N'۶', N'6'), N'۷', N'7'), N'۸', N'8'), N'۹', N'9')
-- 用PARSE指定波斯文化解析为公历DATE
SELECT PARSE(@persianStr AS DATE USING 'fa-IR') AS GregorianDate; -- 返回2024-09-21

方案3：自定义波斯历转公历函数（纯T-SQL）

如果需要更稳定的波斯历转公历逻辑，可编写自定义函数（支持1300-1499年波斯历）：

CREATE FUNCTION dbo.PersianToGregorian(@persianYear INT, @persianMonth INT, @persianDay INT)
RETURNS DATE
AS
BEGIN
    DECLARE @gy INT = 1990, @gm INT = 1, @gd INT = 21
    DECLARE @pyd INT = @persianYear - 1370
    DECLARE @days INT = @pyd * 365 + (@persianMonth - 1) * 31 + (@persianDay - 1)

    -- 调整闰年和月份天数差异
    IF @persianMonth > 6 SET @days = @days - (@persianMonth - 6)
    IF @pyd > 29 SET @days = @days + 1
    IF @pyd > 60 SET @days = @days + 1
    IF @pyd > 91 SET @days = @days + 1

    SET @gd = @gd + @days

    -- 调整为合法公历日期
    WHILE @gd > CASE 
        WHEN @gm IN(1,3,5,7,8,10,12) THEN 31
        WHEN @gm=2 THEN CASE WHEN @gy%4=0 AND (@gy%100!=0 OR @gy%400=0) THEN 29 ELSE 28 END
        ELSE 30
    END
    BEGIN
        SET @gd = @gd - CASE 
            WHEN @gm IN(1,3,5,7,8,10,12) THEN 31
            WHEN @gm=2 THEN CASE WHEN @gy%4=0 AND (@gy%100!=0 OR @gy%400=0) THEN 29 ELSE 28 END
            ELSE 30
        END
        SET @gm = @gm + 1
        IF @gm > 12 BEGIN SET @gm = 1; SET @gy = @gy + 1 END
    END

    RETURN CAST(CONCAT(@gy, '-', @gm, '-', @gd) AS DATE)
END
GO

-- 使用示例：波斯历1403/06/30转公历
SELECT dbo.PersianToGregorian(1403,6,30) AS GregorianDate; -- 返回2024-09-21

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