Hijri Date in SQL Server - with Islamic Month Name

This article focuses on converting the Gregorian date into an Islamic date along with Islamic month name in textual format. In SQL Server there are two methods to convert the Gregorian date to Islamic date, however, for Islamic month name we need to depend on our own code.

Either you can write a function or a stored procedure or you can simply put the month names in your query's conditional clauses.

First, let me show you the methods, SQL Server supports -

SELECT GETDATE() AS [GregorianDate],

       CONVERT(VARCHAR(23),GETDATE(),131) AS [Hijri date]

GO

SELECT GETDATE() AS [GregorianDate], 

       FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss','ar') AS [Hijri date]

GO

In case you need to display the Islamic date in text format like "11 al-Muḥarram 1442" -

SELECT

CONVERT(VARCHAR(2), DATEPART(DAY, FORMAT(GETDATE(),'yyyy-MM-dd','ar'))) + ' ' +

CONVERT(NVARCHAR(30), CASE (DATEPART(MONTH, FORMAT(GETDATE(),'yyyy-MM-dd','ar')))

    WHEN 1 THEN N'al-Muḥarram'

    WHEN 2 THEN N'Ṣafar'

    WHEN 3 THEN N'Rabīʿ al-ʾAwwal'

    WHEN 4 THEN N'Rabīʿ ath-Thānī'

    WHEN 5 THEN N'Jumādā al-ʾAwwal'

    WHEN 6 THEN N'Jumādā ath-Thāniyah'

    WHEN 7 THEN N'Rajab'

    WHEN 8 THEN N'Shaʿbān'

    WHEN 9 THEN N'Ramaḍān'

    WHEN 10 THEN N'Shawwāl'

    WHEN 11 THEN N'Zū al-Qaʿdah'

    WHEN 12 THEN N'Zū al-Ḥijjah'

    END) + ' ' +

CONVERT(VARCHAR(4), DATEPART(YEAR, FORMAT(GETDATE(),'yyyy-MM-dd','ar')))

GO

Do let me know if you find any other way.