CLR To Return Full Gregorian Date By Passed Um Alqura Month And Year

In this article I will show how to create new CLR to be registered in SQL database.

This CLR includes function to convert passed Um Alqura month and year, then returns full Gregorian date.

(I mean by full, the first day of the Um Alqura date with month and year passed converted to Gregorian)

By creating new SQL server project, in visual studio 2012:

Right click on project, add new SQL CLR C# user-defined function:

The beginning of the month: 
Just add the following code:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime ConvertToGregorianStartDate (int Year, int Month)
    {
        System.Globalization.UmAlQuraCalendar UMQ_Calender = new System.Globalization.UmAlQuraCalendar();
        return UMQ_Calender.ToDateTime(Year, Month, 1, 0,0,0,0);
    }
}


The end of the month:
Just add the following code:

[Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime ConvertToGregorianEndDate(int Year, int Month)
    {
        System.Globalization.UmAlQuraCalendar UMQ_Calender = new System.Globalization.UmAlQuraCalendar();
        DateTime firstDayOfTheMonth = UMQ_Calender.ToDateTime(Year, Month, 1, 0, 0, 0, 0);
        return firstDayOfTheMonth.AddMonths(1).AddDays(-1);
    }

Build the project and publish it to your DB.

if you execute the function created, you will get the following result: