מאי18
Written by:
ronen ariely
18/05/2021 21:00 
Background
This post follows the discussion or actually my answer in this old MSDN forum thread. I came back to this topic today following a question asked on Facebook, which requested me for more clarification and reminded me about this old thread. In fact, I started to write this post at that time about two years ago, but I never found the time to finish and publish it.
From time to time I answer a nice question which I think should interesting to all and I do not find such post online, in other cases the answer I present is not trivial and should be shared. With that said, writing posts (especially in a language which is not my native language) is not what I love to focus on. It can take me to long, while in the same time, I can always answer a few more questions and directly help some people. In such cases sometimes I start to writ the post, maybe collect some data, organize all in a folder or a file and... usually forget about it.
After all, till the time that I will want to publish a new post, I will have tens more topics. Probably I need a secretary to do the part that I am no less in writing so that I can focus on finding the solution and the explanation
Well, let's go to the technical part, and present a few SQL Server Bugs and issues when working with Hijri dates
BUG using TRY_CONVER
The definition of the function TRY_CONVERT according to Microsoft documentation is "Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null". In other words, this function should never fails as a result of the input but instead return null if the CONVERT is not succeed!
unfortunately, the attempting to use input string which present a Gregorian date before the first day in the Hijri calendar '0622-07-18' returns error. This can break applications goes against the idea of using TRY_CONVERT function. I consider this as a BUG
DECLARE @DateTime AS DATETIME2
SET @DateTime = '0622-07-17'
SELECT TRY_CONVERT(VARCHAR(11),@DateTime,131) AS [Hijri date]
GO
Error! The date provided is before the start of the Hijri calendar which in Microsoft's 'Kuwaiti Algorithm' is July 15th, 622 C.E. (Julian calendar) or July 18th, 622 C.E (proleptic Gregorian calendar).
I already mentioned above what is reason the value cannot be converted it is simply put of the range of the Hijri calendar and SQL Server fails to deal with it.
ISSUES using FORMAT vs CONVERT
SQL Server supports converting dates into string using the functions CONVERT and FORMAT. You might expect that al roads leads to the same result, but life are always more interesting and unexpected. It turned-out that using FORMAT is not consistent with using CONVERT!
For the sake of the demo, I will create a table with one minion rows. In the first column I will insert the values of the Gregorian dates from the first day in the Hijri calendar (no reason to check dates before it as explain above). In the second and third columns I will calculate the dates by converting them into Hijri date using CONVERT and using FORMAT.
/************** preparation ****************/
-- CREATE numbers table
DROP TABLE if exists NumbersTbl;
GO
DECLARE @RunDate Date
SELECT TOP 1000000 IDENTITY(int,1,1) AS Number
INTO NumbersTbl
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2
CROSS JOIN sys.all_objects s3
ALTER TABLE NumbersTbl ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
GO
SELECT * FROM NumbersTbl
GO
-------------------------------------------------------------------------
-- Calendar Table
DROP TABLE IF EXISTS Gregorian_Hijri_Tbl;
GO
CREATE TABLE Gregorian_Hijri_Tbl(
Gregorian DATETIME2 NOT NULL,
Hijri_UsingConvert VARCHAR(100) NULL,
Hijri_UsingFormat VARCHAR(100) NULL,
Comment NVARCHAR(MAX) NULL
)
GO
DECLARE @StartingDate DATETIME2
SET @StartingDate = '0622-07-17'
INSERT dbo.Gregorian_Hijri_Tbl(Gregorian,Hijri_UsingConvert,Hijri_UsingFormat)
SELECT --TOP 1000
DATEADD(day,Number,@StartingDate) as Gregorian,
TRY_CONVERT(VARCHAR(20),DATEADD(day,Number,@StartingDate),131),
FORMAT (DATEADD(day,Number,@StartingDate), 'd/MM/yyyy hh:mm:ss.', 'ar-SA' )
FROM NumbersTbl
GO -- filing the table can take more the a minutes! Time to take coffee
--ALTER TABLE dbo.Gregorian_Hijri_Tbl
-- ADD CONSTRAINT PK_Gregorian PRIMARY KEY CLUSTERED (Gregorian)
--GO
FORMAT ISSUE: not cover the entire range.
/************** FORMAT ISSUE 1: ****************/
-- Notice that FORMAT does not cover all dates!
SELECT Gregorian,Hijri_UsingConvert,Hijri_UsingFormat
FROM dbo.Gregorian_Hijri_Tbl
GO
SELECT MIN(Gregorian),MIN(Hijri_UsingFormat)
FROM dbo.Gregorian_Hijri_Tbl
WHERE Hijri_UsingFormat IS NOT NULL
GO -- 1900-04-30 00:00:00.0000000 Min date which covered by FORMAT
SELECT MIN(Gregorian),MIN(Hijri_UsingFormat)
FROM dbo.Gregorian_Hijri_Tbl
WHERE Hijri_UsingFormat IS NULL and Gregorian > '1900-04-30 00:00:00.0000000'
GO -- 2077-11-17 00:00:00.0000000 First Min date which again not covered by FORMAT
-- There is no another range which is covered
SELECT MIN(Gregorian),MIN(Hijri_UsingFormat)
FROM dbo.Gregorian_Hijri_Tbl
WHERE Hijri_UsingFormat IS NOT NULL and Gregorian > '2077-11-17 00:00:00.0000000'
GO
As you can see, the FORMAT functions using the language code "ar-SA" covers only dates between 1900-04-30 and 2077-11-16.
FORMAT ISSUE: FORMAT inconsistent with CONVERT
/************** FORMAT ISSUE 2: ****************/
-- The CONVERT and FORMAT functions are not consistent!
-- The inconsistent starts and end at the 29 and 30 days of months in some months
-- Meaning that while FORMAT uses 30 days for the month CONVERT uses 29 days and vise versa
-- For example,
-- Inconsistent starts with Gregorian date 1905-11-27 (last day of month in CONVERT but FIrst day of next month in FORMAT)
-- At the end of that Month at Gregorian date 1905-12-26, the values back to be consistent
SELECT Gregorian,Hijri_UsingConvert,Hijri_UsingFormat, CASE WHEN TRIM(Hijri_UsingConvert)=TRIM(Hijri_UsingFormat) THEN 'OK' ELSE 'inconsistent' END
FROM dbo.Gregorian_Hijri_Tbl t
WHERE t.Gregorian between '1905-11-23 00:00:00.0000000' and '1906-11-23 00:00:00.0000000'
GO
-- Explanation:
-- CONVERT follows the Kuwaiti algorithm (this is documented) and FORMAT using 'ar-SA' follows Arabic (Saudi Arabia) language code which has different rules.
-- Note! I tried to use all the following Arabic language codes and none of them support Kuwaiti algorithm as CONVERT! The only one wjhich returns almost the same is 'ar-SA'
DECLARE @StartingDate DATETIME2
SET @StartingDate = '1906-08-20'
SELECT
@StartingDate as [Gregorian],
TRY_CONVERT(VARCHAR(20),@StartingDate,131) as [UsingConvert],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-sa' ) as [Arabic (Saudi Arabia)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-dz' ) as [Arabic (Algeria)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-bh' ) as [Arabic (Bahrain)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-eg' ) as [Arabic (Egypt)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-iq' ) as [Arabic (Iraq)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-jo' ) as [Arabic (Jordan)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-kw' ) as [Arabic (Kuwait)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-lb' ) as [Arabic (Lebanon)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-ly' ) as [Arabic (Libya)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-ma' ) as [Arabic (Morocco)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-om' ) as [Arabic (Oman)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-qa' ) as [Arabic (Qatar)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-sy' ) as [Arabic (Syria)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-tn' ) as [Arabic (Tunisia)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-ae' ) as [Arabic (U.A.E.)],
FORMAT(@StartingDate, 'd/MM/yyyy hh:mm:ss.', 'ar-ye' ) as [Arabic (Yemen)]
GO
Explanation
Hijri is a calendar system with several variations. For some reason Microsoft chose to implement Hijri calendar using two different algorithm. The function CONVERT uses the Kuwaiti algorithm. The function FORMAT is an CLR function based on he HijriCalendar Class.
My recommendation
Working with Hijri date can be very challenging, and there more that can be said on the topic. As we can see there are two different implementations for Hijri calendar which are used by SQL Server. If you need to support Hijri dates in your side, my recommendation is to based on calendar table and choose which one of the two you want to follow.