SQL Server, UmAlQura Calendar as a CLR-Type


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


 

Comments (6)

  1. O.O says:

    I have a problem with arabic stsings and i hope you can help me

    writing a query like

    select * from mytable where artext=’هدية’

    retrieves both records of

    هدية

    and

    هديت

    sql considers taa and taa marboota as the same character

  2. Dina Lasheen says:

    Dear O.O,

    Thanks for your feedback… I verified this behavior in SQL and it produces this unexpected output. I’ll check back with the sql team and get back to you.

    Thanks,

    Dina

  3. Dina Lasheen says:

    Hi O.O

    The recommended method to add the Arabic collation in the select statement. For example:

    select * from mytable2 where artext=N’هدية’

    collate Arabic_100_CI_AI

    This will retrieve only the first record… taa marboota

  4. Yousry Mohamed says:

    I think there is a small bug in functions like

    public override String ToString()

       {

           return this.DateTime.ToString(ci);

       }

    Currently when I tried it it returned 1st of Ramadan as 21/8/2209 which is not correct as most of current calendars (Try it on Islamic finder site)

    You need to put : ci.DateTimeFormat.Calendar = s_calendar;

    which will return 1st of Ramadan as 22/8/2209

    i.e the previous code was not taking into consideration Um Al Qura calendar

  5. A.Aziem Moemen says:

    Great Article :)

    I followed what you mentioned exactly but I got 1 major problem:

    23-10-2012 (dd-MM-yyyy) appears in Hijri = 2-5-1433 when I use it as CLR Data Type

    and when I use this class in windows application 23-10-2012 (Greg) appears in Hijri as 1-5-1433 !!

    Any idea is appreciated :)