SQL Server Hijri (Hijra) Dates

(Source: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21866389.html)

Hijri Date
The SQL Server datetime data type supports only Gregorian dates, but you can use a conversion function to convert the Gregorian date to the Hijri date based on the Kuwaiti algorithm.

Conversion to and from the Hijri calendar is possible through the CONVERT intrinsic. There are two CONVERT styles that are available in SQL Server 2000 to support Hijri dates:

130—Returns the date using the Hijri calendar, in dd mon yyyy hh:mi:ss:mmmAM format.
131—Returns the date using the Hijri calendar, in dd/mm/yy hh:mi:ss:mmmAM format.
For example, to convert a Gregorian date to Hijri format with 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 convert a Hijri date to Gregorian format:

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

This query would convert the date to SQL Server datetime type, which in SQL Query Analyzer would appear as follows:

2000-08-07 12:14:35.727

Note that SQL Server does not use the regional calendar settings included with Windows 2000, and any adjustment made in that regional setting for the Hijri date does not affect the conversion method of SQL Server.

Also, SQL Server date functions such as DateName, DatePart, and DateAdd work with the Gregorian calendar, so you must make your conversions programmatically during development.

Best regards,
Mohammad Al-Mohsin
http://mem9.net | http://mem999.spaces.live.com

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS