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

Hijridate_convertFunction

SELECT
GETDATE() AS [GregorianDate], 
       FORMAT(GETDATE(),‘yyyy-MM-dd hh:mm:ss’,‘ar’) AS [Hijri date]
GO

format_withDateTime

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

convert into islamic month text


Do let me know if you find any other way. 

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s