如何在使用Parse函数时控制日历系统？

Question

我需要在Server中根据.net DateTime类型将字符串转换为在不同区域性中格式化的日期时间。对于大多数区域性，我可以使用Server 解析函数轻松地做到这一点。

SELECT
PARSE(N'4/22/1996 11:00:00 PM' AS DATETIME USING 'en-US' ),
PARSE(N'6/7/2016' AS DATETIME USING 'en-US' ),
PARSE(N'1/2/2016' AS DATETIME USING 'en-US' ),
PARSE(N'2/1/2016' AS DATETIME USING 'en-US' ),
PARSE(N'1:00:00 AM' AS DATETIME USING 'en-US' )

产出：

+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| 1996-04-22 23:00:00.000 | 2016-06-07 00:00:00.000 | 2016-01-02 00:00:00.000 | 2016-02-01 00:00:00.000 | 2016-12-05 01:00:00.000 |
+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

对于默认情况下不基于公历系统的区域性，我不知道如何告诉Parse函数使用哪个日历系统。

例如，对于ar-SA，默认的日历系统是hijri。当日期是hijri dates时，我可以使用解析函数：

SELECT
PARSE(N'05/12/16 11:00:00 م' AS DATETIME USING 'ar-SA' ),
PARSE(N'02/09/37' AS DATETIME USING 'ar-SA' ),
PARSE(N'22/03/37' AS DATETIME USING 'ar-SA' ),
PARSE(N'22/04/37' AS DATETIME USING 'ar-SA' ),
PARSE(N'01:00:00 ص' AS DATETIME USING 'ar-SA' )

输出：

+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| 1996-04-22 23:00:00.000 | 2016-06-07 00:00:00.000 | 2016-01-02 00:00:00.000 | 2016-02-01 00:00:00.000 | 2016-12-05 01:00:00.000 |
+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

但是，我所拥有的字符串是使用ar-SA日历系统在格里高利中形成的。

当我试图使用解析函数转换它们时

SELECT
PARSE(N'22/04/1996 11:00:00 م' AS DATETIME USING 'ar-SA' ),
PARSE(N'07/06/2016' AS DATETIME USING 'ar-SA' ),
PARSE(N'02/01/2016' AS DATETIME USING 'ar-SA' ),
PARSE(N'01/02/2016' AS DATETIME USING 'ar-SA' ),
PARSE(N'01:00:00 ص' AS DATETIME USING 'ar-SA' )

我得到以下错误：

使用区域性'ar-SA‘将字符串值'22/04/1996 11:00:00م’转换为数据类型日期时间时出错。

，但是我需要一种方法来告诉解析字符串日期时间在哪个日历系统中，这样我就得到了预期的：输出

+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| 1996-04-22 23:00:00.000 | 2016-06-07 00:00:00.000 | 2016-01-02 00:00:00.000 | 2016-02-01 00:00:00.000 | 2016-12-05 01:00:00.000 |
+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

是否有方法调用解析函数并告诉Server与区域性一起使用哪个日历系统？

更新

皈依不起作用。它不处理阿拉伯文am/pm的名称，也假定了hijri日历系统。

SELECT
CONVERT(datetime, N'22/04/1996 11:00:00 م', 131 ),
CONVERT(datetime, N'07/06/2016', 131 ),
CONVERT(datetime, N'02/01/2016', 131 ),
CONVERT(datetime, N'01/02/2016', 131 ),
CONVERT(datetime, N'01:00:00 ص', 131 )

产出：

从字符串转换日期和/或时间时，转换失败。

当非巴黎AM PM指定仍然使用Hijiri日历系统时

SELECT
CONVERT(datetime, N'22/04/1996 11:00:00 pm', 131 ),
CONVERT(datetime, N'07/06/2016', 131 ),
CONVERT(datetime, N'02/01/2016', 131 ),
CONVERT(datetime, N'01/02/2016', 131 ),
CONVERT(datetime, N'01:00:00 am', 131 )

它导致日期完全错误：

+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| 2558-06-10 23:00:00.000 | 2577-12-18 00:00:00.000 | 2577-07-18 00:00:00.000 | 2577-08-16 00:00:00.000 | 1900-01-01 01:00:00.000 |
+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

Answer 1

我只能假设没有默认的方法来做到这一点。这是基于在执行日期时间解析时无法找到控制日历的引用的任何材料。

我创建了一个CLR标量值函数来执行总是使用Gregorian日历的转换。

用法：

SELECT
'en-US',
dbo.ConvertGregorianDateTime(N'4/22/1996 11:00:00 PM', 'en-US' ),
dbo.ConvertGregorianDateTime(N'6/7/2016', 'en-US' ),
dbo.ConvertGregorianDateTime(N'1/2/2016', 'en-US' ),
dbo.ConvertGregorianDateTime(N'2/1/2016', 'en-US' ),
dbo.ConvertGregorianDateTime(N'1:00:00 AM', 'en-US' )
UNION ALL
SELECT
'ar-SA',
dbo.ConvertGregorianDateTime(N'22/04/1996 11:00:00 م', 'ar-SA' ),
dbo.ConvertGregorianDateTime(N'07/06/2016', 'ar-SA' ),
dbo.ConvertGregorianDateTime(N'02/01/2016', 'ar-SA' ),
dbo.ConvertGregorianDateTime(N'01/02/2016', 'ar-SA' ),
dbo.ConvertGregorianDateTime(N'01:00:00 ص', 'ar-SA' )

输出：

+-------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| en-US | 1996-04-22 23:00:00.000 | 2016-06-07 00:00:00.000 | 2016-01-02 00:00:00.000 | 2016-02-01 00:00:00.000 | 2016-12-07 01:00:00.000 |
+-------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| ar-SA | 1996-04-22 23:00:00.000 | 2016-06-07 00:00:00.000 | 2016-01-02 00:00:00.000 | 2016-02-01 00:00:00.000 | 2016-12-07 01:00:00.000 |
+-------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

来源：

using Microsoft.SqlServer.Server;
using System;
using System.Globalization;
using System.Linq;

public static class UserDefinedFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
    public static DateTime ParseGregorianDateTimeString(string dateTimeAsString, string cultureName)
    {
        var culture = new CultureInfo(cultureName);

        culture.DateTimeFormat.Calendar = culture.FindBestGregorianCalendar();

        return DateTime.Parse(dateTimeAsString, culture);
    }

    private static Calendar FindBestGregorianCalendar(this CultureInfo culture)
    {
        var bestGregorianCalendar = (from calendar in culture.OptionalCalendars
                                     where calendar is GregorianCalendar
                                     orderby (calendar as GregorianCalendar).CalendarType == GregorianCalendarTypes.Localized ? 0 : 1
                                     select calendar).FirstOrDefault();
        if (bestGregorianCalendar == null)
        {
            throw new NotSupportedException(string.Format("The current locale [{0}] is not supported because it doesn't support the Gregorian Calendar System", culture.Name));
        }
        return bestGregorianCalendar;
    }
}

将程序集添加到Server的SQL脚本：

CREATE ASSEMBLY [clrFunction]
FROM 'C:\UserDefinedCLRFunctions\clrFunction.dll'
WITH PERMISSION_SET = SAFE

创建函数的SQL脚本：

CREATE FUNCTION ConvertGregorianDateTime(@date as nvarchar(MAX), @locale as nvarchar(10))
RETURNS DateTime
AS external name [clrFunction].UserDefinedFunctions.ConvertGregorianDateTimeString

Answer 2

是否有一种方法可以调用解析函数并告诉Server与区域性一起使用哪个日历系统？没有，但您可以将语言更改为'British'，以切换到公历。

我无法用阿拉伯文am/pm解析gregorian日期，所以所有这些都用am/pm代替了它。

例如：http://rextester.com/ZVAG17162

if exists (select * from tempdb.sys.objects where name like '#Hijri%') begin; drop table #Hijri; end;
create table #Hijri (strIn nvarchar(32) 
                   , amPm nvarchar(32))
/* Hijri Format & Gregorian Calendar */ insert into  #Hijri (strIn) values (N'22/04/1996 11:00:00 م'),(N'07/06/2016'),(N'02/01/2016'),(N'01/02/2016'),(N'01:00:00 ص')
/* Hijri Format & Hijri Calendar */  --insert into  #Hijri (strIn) values (N'05/12/16 11:00:00 م') ,(N'02/09/37') ,(N'22/03/37') ,(N'22/04/37') ,(N'01:00:00 ص') 
update #Hijri set amPm = replace(replace(strIn,N' م',N' pm'),N' ص',N' am');
--set language 'Arabic';  /* implies Hijri Calendar                                            */
--set language 'English'; /* implies Gregorian Calendar & implicitly sets dateformat mdy       */
set language 'British';   /* implies Gregorian Calendar & implicitly sets dateformat dmy       */
--set dateformat mdy;     /* requires style 103 on convert, overrides language default         */
--set dateformat dmy;     /* does not require style 103 on convert, overrides language default */

select 
    strIn
  , amPm
  , GregorianCalendarDateTime     =convert(datetime2(2),amPm,103)
  --/*  -- requires: set language 'British'; -- or other gregorian calendar culture -- else converts to Hijri Calendar
  , HijriFormatGregorianCalendar  =replace(replace(
                                  format(convert(datetime2(2),amPm,103), N'dd/MM/yyyy hh:mm:ss tt') 
                                  ,N' PM',N' م'),N' AM',N' ص')   --*/
  , HijriFormatHijriCalendar      =format(convert(datetime2(2),amPm,103), N'dd/MM/yyyy hh:mm:ss tt', 'ar-SA' )
  , HijriCalendarDateString       =convert(nvarchar(32),convert(datetime2(2),amPm,103),131)
  --/* -- requires: set language 'Arabic' or 'British'; or set dateformat dmy;
  , HijriCalendarDateTime         =convert(datetime2(2),(convert(nvarchar(32),convert(datetime2(2),amPm,103),131)),103)  --*/
  from #Hijri;

回复评论：“只是想知道你是否有一个链接，你可以指给我说，日历系统无法控制？”数据、日期时间、datetime2和日期时间偏移数据类型不存储可变日历类型，它们是Gregorian。当您看到一个hijri时，您看到的是一种应用于8字节日期时间值(或x字节datetime2值)的格式。

sql服务器日期时间数据类型和sql server datetime2数据类型参考链接

甚至连DateTime中的.NET框架4.6.2都在使用公历。

//此值类型表示日期和时间。每个DateTime //对象都有一个Int64类型的私有字段(Ticks)，该字段将//日期和时间存储为自公元1年1月1日// 12:00以来的100纳秒间隔。

.NET框架DateTime构造器只接受一个Calendar参数来解释年、月和日。

当使用带区域性参数的解析时，用于标识表示日期时间值的字符串值的格式。它使用的方式与转换接受一个样式参数的方式相同，该参数确定字符串值的标准(格式)。专门针对阿拉伯文化'ar-SA‘和样式130和131，Server使用科威特算法。