Thursday, October 16, 2008

Handling of Hijri Calendar in Microsoft Products

It was interesting to me to see how Microsoft was handling Hijri dates, as we know that Muslims follow the lunar calendar. They are using an algorithm called "Kuwaiti Algorithm", which they haven't exposed it to the public. But this alogorithm has been developed after extensive analysis.

It is also interesting to see that they have a special flag to convert gregorian dates to Hijri, and vice versa in SQL Server 2000, SQL Server 2005 and SQL Server 2008 as well.

There are two CONVERT styles that are available in SQL Server 2000 to support Hijri dates:

130 - Returns the date via the Hijri calendar, in dd mon yyyy hh:mi:ss:mmmAM format.

131 - Returns the date via the Hijri calendar, in dd/mm/yy hh:mi:ss:mmmAM format.

For example, to convert a date to Hijri format in Transact-SQL, you would use syntax such as the following:

SELECT CONVERT(nchar, GETDATE(), 131) 

This query will return a string such as the following in its result set:

7/05/1421 12:14:35:727PM 

The reverse operation is also possible. The following syntax would be used to go in the opposite direction:

SELECT CONVERT(datetime, ' 7/05/1421 12:14:35:727PM', 131) 

This query would convert the date to SQL Server datetime type, which in Query Analyzer would look like:

2000-08-07 12:14:35.727
You find more details about the above discussion, follow this link: