SQL与C#中将日期转换为ar-SA文化时日期不符问题求助

问题背景 #

使用SQL Server的FORMAT函数或C#的ar-SA文化转换公历日期2024-06-03时，得到的伊斯兰历结果为1445/11/26，但正确的沙特官方乌姆·古拉历日期应为1445/11/25，尝试CAST/CONVERT/PARSE等多种转换方式均无法修正偏差。

问题原因 #

.NET和SQL Server默认的ar-SA文化使用的是数学算法推导的近似伊斯兰历，而沙特阿拉伯官方采用的是乌姆·古拉历（Umm al-Qura Calendar）——该历法基于实际新月观测结果，部分日期与算法推导值存在差异。

解决方案 #

1. C#中直接使用乌姆·古拉历类 #

从.NET Core 3.0及.NET 5开始，官方提供了UmAlQuraCalendar类，可直接实现精准转换：

DateTime gregorianDate = DateTime.Parse("2024-06-03");
UmAlQuraCalendar umAlQura = new UmAlQuraCalendar();

int hijriYear = umAlQura.GetYear(gregorianDate);
int hijriMonth = umAlQura.GetMonth(gregorianDate);
int hijriDay = umAlQura.GetDayOfMonth(gregorianDate);

string correctHijriDate = $"{hijriYear:D4}/{hijriMonth:D2}/{hijriDay:D2}";
// 输出结果：1445/11/25

2. SQL Server中通过CLR集成实现 #

SQL Server本身无内置乌姆·古拉历支持，可通过CLR函数封装C#的UmAlQuraCalendar逻辑：

步骤1：编写C# CLR代码并编译为DLL

using System;
using System.Data.SqlTypes;
using System.Globalization;

public class HijriCalendarConverter
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ConvertGregorianToUmAlQura(SqlDateTime gregorianDate)
    {
        if (gregorianDate.IsNull)
            return SqlString.Null;
            
        DateTime date = gregorianDate.Value;
        UmAlQuraCalendar calendar = new UmAlQuraCalendar();
        
        int year = calendar.GetYear(date);
        int month = calendar.GetMonth(date);
        int day = calendar.GetDayOfMonth(date);
        
        return new SqlString($"{year:D4}/{month:D2}/{day:D2}");
    }
}

步骤2：在SQL Server中注册CLR程序集并创建函数

-- 启用CLR集成
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

-- 创建程序集（替换为你的DLL实际路径）
CREATE ASSEMBLY UmAlQuraConversion
FROM 'C:\YourProjectPath\UmAlQuraConverter.dll'
WITH PERMISSION_SET = SAFE;
GO

-- 创建自定义转换函数
CREATE FUNCTION dbo.ConvertToUmAlQura(@gregorianDate DATETIME)
RETURNS NVARCHAR(10)
AS EXTERNAL NAME UmAlQuraConversion.HijriCalendarConverter.ConvertGregorianToUmAlQura;
GO

-- 使用示例
SELECT dbo.ConvertToUmAlQura('2024-06-03'); -- 输出：1445/11/25

3. 备选方案：自定义SQL函数（基于日期映射） #

若无法启用CLR，可维护公历-乌姆·古拉历的日期映射表，或基于沙特官方公布的日期偏移规则编写自定义函数，但这种方式需定期更新偏移数据，准确性逊于CLR方法。

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