I had a customer question about using Um Al Qura calendar in SQL Server. There is an existing implementation in the .NET Framework, starting from version 2.0 of UmAlquraCalendar. So what you need to do is define a new CLR type based on the UmAlQuraCalendar and then use it in SQL server, either define fields or to convert the CLR types that you can use in the code.

First step, create a user-defined type to be used in SQL server. This is a sample code to show how to create and use a CLR data type.

If you have Visual Studio, then follow these steps:

  1. Create a new Database project in the Visual C# language nodes.
  2. Add a reference to the SQL Server database that will contain the user-defined data types.
  3. Add a User-Defined Type class.
  4. Copy this code that implements the UmAlQura Calendar :

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Globalization;

[Serializable]

[SqlUserDefinedType(Format.Native, IsByteOrdered = true)]

public struct UmAlQuraDate : INullable

{

    private long dtTicks;

    private bool isNull;

    // Calendar object used for all calendar-specific operations

    private static readonly UmAlQuraCalendar s_calendar =

        new UmAlQuraCalendar();

    // For correct formatting we need to provide a culture code for

    // a country that uses the Um Al Qura calendar: Saudi Arabia.

    private static readonly CultureInfo ci =

        new CultureInfo("ar-SA", false);

    // get a null instance

    public static UmAlQuraDate Null

    {

        get

        {

            UmAlQuraDate dt = new UmAlQuraDate();

            dt.isNull = true;

            return dt;

        }

    }

 

    public bool IsNull

    {

        get

        {

            return this.isNull;

        }

    }

    public UmAlQuraDate(long ticks)

    {

        isNull = false;

        dtTicks = ticks;

    }

 

    public UmAlQuraDate(DateTime time)

        : this(time.Ticks)

    {

    }

 

    public static UmAlQuraDate Parse(SqlString s)

    {

        if (s.IsNull) return Null;

        DateTime t = DateTime.Parse(s.Value);

        return new UmAlQuraDate(t);

    }

 

    public static UmAlQuraDate ParseArabic(SqlString s)

    {

        if (s.IsNull) return Null;

        DateTime t = DateTime.Parse(s.Value, ci);

        return new UmAlQuraDate(t);

    }

 

    public static UmAlQuraDate FromSqlDateTime(SqlDateTime d)

    {

        if (d.IsNull) return Null;

        return new UmAlQuraDate(d.Value);

    }

 

    public static UmAlQuraDate Now

    {

        get

        {

            return new UmAlQuraDate(DateTime.Now);

        }

    }

 

    public DateTime DateTime

    {

        get { return new DateTime(this.dtTicks); }

    }

 

    public SqlDateTime ToSqlDateTime()

    {

        return new SqlDateTime(this.DateTime);

    }

 

    public override String ToString()

    {

        return this.DateTime.ToString(ci);

    }

 

    public String ToStringUsingFormat(String format)

    {

        return this.DateTime.ToString(format, ci);

    }

 

    public int Year

    {

        get

        {

            return s_calendar.GetYear(this.DateTime);

        }

    }

 

    public int Month

    {

        get

        {

            return s_calendar.GetMonth(this.DateTime);

        }

    }

 

    public int Day

    {

        get

        {

            return s_calendar.GetDayOfMonth(this.DateTime);

        }

    }

 

    public UmAlQuraDate AddYears(int years)

    {

        return new

            UmAlQuraDate(s_calendar.AddYears(this.DateTime, years));

    }

 

    public UmAlQuraDate AddDays(int days)

    {

        return new

            UmAlQuraDate(s_calendar.AddDays(this.DateTime, days));

    }

 

    public double DiffDays(UmAlQuraDate other)

    {

        TimeSpan diff = DateTime.Subtract(other.DateTime);

        return diff.Days;

    }

}

 

  1. From the Build menu, select Deploy. This registers the assembly and creates the type in the SQL Server database.

For more information about how to register the User-Defined Types in SQL Server please check this article, http://msdn.microsoft.com/en-us/library/ms131079.aspx

 

Second step,Use the UmAlQuraDate to do conversion or declare types in your database tables.

 

To use the CLR data type in SQL server Management Studio , you first need to enable CLR, or you will receive the following error message: “Execution of user code in the .NET Framework is disabled. Enable ”clr enabled’ configuration option.” To fix this, run

exec sp_configure 'clr enabled', 1

then:

RECONFIGURE

You can now use the CLR data type.

This is an example that accepts UmAlQura dates and displays them:

 

DECLARE @MyDate UmAlQuraDate

SET @MyDate = UmAlQuraDate::ParseArabic('01/02/1430')

PRINT @MyDate.ToStringUsingFormat('F')

Output:

01/صفر/1430 12:00:00 ص

To convert from Gregorian date to Um Al Qura date, check this example:

DECLARE @gregd DateTime

DECLARE @MyDate UmAlQuraDate

SET @gregd = 'February 20, 2009'

SET @MyDate = UmAlQuraDate::FromSqlDateTime(@gregd)

PRINT N'Gregorean Date: ' + CAST(@gregd AS nvarchar(30));

PRINT N'Um AlQura Date (short format):' + @MyDate.ToString();

PRINT N'Um AlQura Date (long format):' + @MyDate.ToStringUsingFormat('F')

This is the output:

Gregorean Date: Feb 20 2009 12:00AM

Um AlQura Date (short format):25/02/30 12:00:00 ص

Um AlQura Date (long format):25/صفر/1430 12:00:00 ص

 

To convert from Um Al Qura date to Gregorian, check this example:

DECLARE @MyDate UmAlQuraDate

SET @MyDate = UmAlQuraDate::ParseArabic('01/09/1430')

PRINT N'Gregorean Date: ' + CAST(@MyDate.ToSqlDateTime() AS nvarchar(30));

PRINT N'Um AlQura Date (short format):' + @MyDate.ToString();

PRINT N'Um AlQura Date (long format):' + @MyDate.ToStringUsingFormat('F')

Output is:

Gregorean Date: Aug 21 2009 12:00AM

Um AlQura Date (short format):01/09/30 12:00:00 ص

Um AlQura Date (long format):01/رمضان/1430 12:00:00 ص

 

In addition to the above, you can also create a UmAlQuraDate field

Create the Table:

CREATE TABLE DateTable (

   GregDate DateTime,

   UmAlQura UmAlQuraDate

)

Convert Western Dates to UmAlQura dates in  the table:

update DateTable

set UmAlQura = UmAlQuraDate::FromSqlDateTime(GregDate)

Retrieve the value of the UmAlQura dates in  the table, you need to type cast to String:

SELECT UmAlQura.ToString()

FROM DateTable

I hope you find this useful