Here is another possible solution. You can store your dates in DATETIME data type (
Gregorian calendar) and use convert functions between
Gregorian and
Hijri dates.
EXAMPLES:
Hijri date to Gregorian date:
DECLARE @HijriDate VARCHAR(10) = '02/02/1435';
SELECT CONVERT(DATETIME, @HijriDate, 131);
Gregorian date: 2013-12-05 00:00:00.000
Gregorian date to Hijri date:
DECLARE @GregorianDate DATETIME = '02/13/2014';
SELECT CONVERT(VARCHAR(25), @GregorianDate, 131);
Hijri date: 13/04/1435 12:00:00:000AM
You can find more information here:
1.
SQL Server Hijri (Hijra) Dates[
^]
2.
SQL SERVER – How to convert Gregorian dates to Hijri date with Formatting[
^]
---------------------------------------------------------------------------------------
THIS IS IMPORTANT!!!
I did some research and found out this:
Unfortunately, this solution works correctly only for the Kuwaiti Hijri calendar.
I'm going to illustrate the problem. According to Umm al-Qura calendar
30/02/1434 is a valid date (matches
01/12/2013). But if you try this:
DECLARE @HijriDate VARCHAR(10) = '30/02/1434';
SELECT CONVERT(DATETIME, @HijriDate, 131);
you are going to get this:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
This happened because according to the Kuwaiti algorithm
30/02/1434 is
not a valid date. "Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm." -
Microsoft. In comparison,
Google Calendar currently supports three major variations of Hijri calendar.
Other possible solutions:
1. Look at the
solution 1.
2. Store your dates as DATETIME in
SQL Server (Gregorian) and implement date conversion in ASP.NET (Hijra, Umm al-Qura). This might be useful:
Um Al Qura (Hijri) Calendar[
^]