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:
- Create a new Database project in the Visual C# language nodes.
- Add a reference to the SQL Server database that will contain the user-defined data types.
- Add a User-Defined Type class.
- 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;
}
}
- 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
