Showing posts with label Islamic Month Name in SQL. Show all posts
Showing posts with label Islamic Month Name in SQL. Show all posts

Saturday, August 29, 2020

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. 

Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...