How to control the calendar system when using the Parse function?

Question

I have the need in SQL Server to convert strings to date times that are formatted in different cultures based off of the .net DateTime type. I can do that easily for most cultures using the SQL Server parse function.

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' )

output:

+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| 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 |
+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

For cultures that are not, by default, based on the gregorian calendar system I cannot figure out how to tell the Parse funtion which calendar system to use.

For example, for ar-SA, the default calendar system is hijri. I can use the parse function as along as the dates are 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' )

output:

+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| 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 |
+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

However, The strings that I have are formated in ar-SA using the Gregorian calendar system.

When I try to convert them using the parse function

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' )

I get the following error:

Error converting string value '22/04/1996 11:00:00 م' into data type datetime using culture 'ar-SA'.

But I need a way to tell parse what calendar system the string datetimes are in so I get the expected output of:

+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| 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 |
+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

Is there a way to call the parse function and tell SQL Server which calendar system to use along with the culture?

Update

Convert doesn't work. It doesn't handle the Arabic am/pm designations and also assumes the hijri calendar system.

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 )

output:

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

When non aribic AM PM designation still uses Hijiri calendar system

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 )

It results in the date being completly wrong:

+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| 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 |
+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

For those of you who don't understand the difference between the [Hijiri calendar](https://en.wikipedia.org/wiki/Islamic_calendar) and [Gregorian calendar](https://en.wikipedia.org/wiki/Gregorian_calendar) please do some research before answering. Dates are represented not only in different formats but the same point in time is represented by different values. — Aaron Carlson, Dec 06 '16 at 13:39

SqlZim · Answer 1 · 2016-12-07T16:51:48.140

Is there a way to call the parse function and tell SQL Server which calendar system to use along with the culture? No, but you can change the language to 'British' to switch to the gregorian calendar.

I wasn't able to parse gregorian dates with the arabic am/pm, so all of this replaces that with am/pm.

e.g.: 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;

Response to comment: "Just wondering if you have a link you can point me to that states that the calendar system cannot controlled?" The data, datetime, datetime2, and datetimeoffset data types do not store a variable calendar type, they are Gregorian calendars. When you see a hijri datetime, you are seeing a format applied to a 8 byte datetime value (or x byte datetime2 value).

Reference links for sql server datetime data type and sql server datetime2 data type

Even the DateTime in .NET Framework 4.6.2 is using the Gregorian Calendar.

// This value type represents a date and time.  Every DateTime 
// object has a private field (Ticks) of type Int64 that stores the 
// date and time as the number of 100 nanosecond intervals since 
// 12:00 AM January 1, year 1 A.D. in the proleptic Gregorian Calendar.

The .NET Framework DateTime Constructor takes a Calendar parameter only to interpret year, month, and day.

When you are using parse with the culture argument, that is used to identify the format of the string value that represents a datetime value. It is using that in the same way that convert takes a style parameter which determines the standard (format) of the string value. Specifically for Arabic culture 'ar-SA' and styles 130 and 131, SQL Server uses the Kuwaiti algorithm.

I did not accidentally switch formats. Those are the formats of the date times that I need to convert. The specific ones are formatted in the ar-SA locale using the Gregorian calendar system. — Aaron Carlson, Dec 06 '16 at 13:33
alright, but this still converts those values to datetime format. Can you clean up your question so you just tell us what your actual input will be and what your desired output would be instead of showing us all the things you can and can't do? — SqlZim, Dec 06 '16 at 13:45
I have added what exactly I am looking for when converting ar-SA date formats created with the Gregorian calendar system. — Aaron Carlson, Dec 06 '16 at 13:51
My Question, which is stated above, Is this: **Is there a way to call the parse function and tell SQL Server which calendar system to use along with the culture?** My example uses ar-SA formatted dates in the Gregorian calendar system. Your answer is not using ar-SA formatted dates. — Aaron Carlson, Dec 06 '16 at 14:18
Just wondering if you have a link you can point me to that states that the calendar system cannot controlled? — Aaron Carlson, Dec 07 '16 at 15:26
I expanded my answer to address your request for clarification. In short, you can see that the Calendar type doesn't change for the `datetime` data type. `datetime` data type reference: https://msdn.microsoft.com/en-us/library/ms187819.aspx and `parse` function reference https://msdn.microsoft.com/en-us/library/hh213316.aspx — SqlZim, Dec 07 '16 at 16:54

Aaron Carlson · Accepted Answer · 2016-12-07T21:09:46.970

I can only assume there is no default way to do this. This is based on not being unable to find any material that references controlling the calendar when doing datetime parsing.

I created a CLR Scalar Value Function to do the conversion that always uses the Gregorian calendar.

Usage:

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' )

Output:

+-------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+
| 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 |
+-------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+

Source:

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;
    }
}

SQL Script to add the assembly to SQL Server:

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

SQL script to create the function:

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

How to control the calendar system when using the Parse function?

2 Answers2