You have a problem with Hijri dates and SQL Server?

Let’s first discuss the problem. We use Hijri calendar and SQL server uses Gregorian calendar. And SQL server has restriction on the Gregorian dates earlier than 1753.

But SQL server saves dates internally in another format (It’s neutral and doesn’t depend on any calendar)

So we need to convert the dates directly from SQL Server internal format to Hijri calendar format.

To accomplish this we will use the convert function to convert the dates into Hijri calendar and save it into nvarchar column. This will solve the problem of entering the year 1425 (current year in Hijri) and also will give you the ability to save the Hijri dates in Arabic names.

But we still have the problem of sorting!! So we need to add the convert statement in the order by statement to force SQL Server to deal with our dates as dates.

This is the theoretical part. Let’s have example.

First we will build test table with this script

/************For demonstration purpose only and MUST be tested before usage **********************/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[test]

GO

CREATE TABLE [dbo].[test] (

[date] [nvarchar] (50) COLLATE Arabic_CI_AI NULL

) ON [PRIMARY]

GO

/**********************************************************************************/

To add dates to our table use this script

/************For demonstration purpose only and MUST be tested before usage **********************/

insert test

values(convert(nchar(30),getdate(),131))

/**********************************************************************************/

This script will add the Hijri date as 8/01/1425 & If you want to add it in the format 8 محرم 1425 (Arabic months names) use this script

/************For demonstration purpose only and MUST be tested before usage **********************/

insert test

values(convert(nchar(30),getdate(),130))

/**********************************************************************************/

Ok. What’s next?

We need to sort these dates in Ascending or descending order so we have to reconvert these character data into dates again

/************For demonstration purpose only and MUST be tested before usage **********************/

select * from test order by convert(datetime,[date],131)

/**********************************************************************************/

Have fun with dates,

Mohamed