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]

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’)))   WHEN1 THEN N’al-Muḥarram’
  WHEN2 THEN N’Ṣafar’
  WHEN3 THEN N’Rabīʿ al-ʾAwwal’
  WHEN4 THEN N’Rabīʿ ath-Thānī’
  WHEN5 THEN N’Jumādā al-ʾAwwal’
  WHEN6 THEN N’Jumādā ath-Thāniyah’
  WHEN7 THEN N’Rajab’
  WHEN8 THEN N’Shaʿbān’
  WHEN9 THEN N’Ramaḍān’
  WHEN10 THEN N’Shawwāl’
  WHEN11 THEN N’Zū al-Qaʿdah’
  WHEN12 THEN N’Zū al-Ḥijjah’
  END) + ‘ ‘ +
CONVERT(VARCHAR(4), DATEPART(YEAR, FORMAT(GETDATE(),‘yyyy-MM-dd’,‘ar’)))
GO