none
Error in converting Hijri Date to Gregorian Date RRS feed

  • Question

  • I get an error when I try to convert the following Hijri Date to Gregorian Date

    '1434-02-30'

    I used:

    select

    CONVERT(date,'1434-02-30',131)

    Later on I have discovered that the conversion fails for 30th of Safar of any other Hijri year such as

    '1435-02-30'

    '1436-02-30'

    so can you please help me to overcome this issue. with many Thanks in advance

    Thursday, February 8, 2018 3:09 PM

Answers

All replies

  • The conversion documentation shows that the input for Hijri dates should be in the format of "dd/mm/yyyy". Is 30/02/1434 a valid date? When I do the conversion of 29/02/1434 and 01/03/1434, they are returned as consecutive days.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

    select CONVERT(date,'28/02/1434',131)  -- Returns 2013/01/10
    select CONVERT(date,'29/02/1434',131)  -- Returns 2013/01/11
    select CONVERT(date,'01/03/1434',131)  -- Returns 2013/01/12
    select CONVERT(date,'02/03/1434',131)  -- Returns 2013/01/13
    Thanks,
    Sam Lester (MSFT)


    https://blogs.msdn.microsoft.com/samlester/

    Twitter - @SQLSamLester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, February 9, 2018 10:55 AM
    Moderator
  • One thing to note is that Microsoft's conversions are based on Kuwaiti Algorithm which is one of the available versions

    https://en.wikipedia.org/wiki/Tabular_Islamic_calendar


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, February 9, 2018 11:19 AM
  • First of all, Many Thanks for your help, but the problem still exist, since still I get the same earlier error 

    select CONVERT(date,'30/02/1434',131)  -- it should Return 2013-01-12, but I get Conversion failed when converting date and/or time from character string.

    Notice that :

    select FORMAT ( Cast('2013-01-12' as Date), 'yyyy-MM-dd', 'ar-SA' ) --Returns 1434-02-30

    Saturday, February 10, 2018 10:08 AM
  • This has been a really interesting thread and I have learned a lot from it. My thought here is that the issue is that the CONVERT function does not allow for a culture parameter, while the FORMAT function allows it, which leads to the issue you are seeing. Since there are numerous Arabic cultures (from a .NET perspective), you can distinguish between them in the FORMAT function, but not with the CONVERT function, which defaults to the "at-KW" (Kuwait) culture, as Visakh mentioned and is shown in the documentation.

    In this code, notice how the Saudi Arabia culture returns a different result than Algeria, Kuwait, Lebanon, and Libya cultures.

    -- "ar-SA" culture returns different output than that of other Arabic culture (DZ, KW, LB, LY)
    select FORMAT ( Cast('2013-01-13' as Date), 'yyyy-MM-dd', 'ar-SA' ) -- Arabic - Saudi Arabia -- Returns 1434-03-01
    select FORMAT ( Cast('2013-01-13' as Date), 'yyyy-MM-dd', 'ar-DZ' ) -- Arabic - Algeria -- Returns 2013-01-13
    select FORMAT ( Cast('2013-01-13' as Date), 'yyyy-MM-dd', 'ar-KW' ) -- Arabic - Kuwait -- Returns 2013-01-13
    select FORMAT ( Cast('2013-01-13' as Date), 'yyyy-MM-dd', 'ar-LB' ) -- Arabic - Lebanon  -- Returns 2013-01-13
    select FORMAT ( Cast('2013-01-13' as Date), 'yyyy-MM-dd', 'ar-LY' ) -- Arabic - Libya -- Returns 2013-01-13

    I think what that means to you is that if you are using the Saudi Arabia (at-SA) culture, then the CONVERT function won't be able to get the values you expect. It's a lot more manual, but one option might be to create an at-SA date table containing the range of years/dates you need to convert, then create a function that you can call in your code for this custom created format function.

    The request back to Microsoft through Connect/UserVoice would be to add a culture input into the CONVERT function so that you could provide this value during the built-in function call. If you file this request, please provide the link back in this thread and I'll help share it to generate additional up-votes.

    Does that make sense?

    Thanks,
    Sam Lester (MSFT)


    https://blogs.msdn.microsoft.com/samlester/

    Twitter - @SQLSamLester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, February 10, 2018 10:38 AM
    Moderator
  • Many Thanks, but notice that '1434-02-30' is a valid Kuwait Hijri Date, (yyyy-02-30 is accepted Hijri Date for all Arab countries), so I guess the problem is that "as you stated earlier" the Convert function expects an input which should be a valid Gregorian date, and since 'yyyy-02-30' is not a valid Gregorian date then the conversion fails, Moreover notice that, I obtained the same error message when I tried to execute the following:

    select cast('1434-02-30' as date) --Conversion failed when converting date and/or time from character string.

    so, for now I guess there is no work-around other than manual mapping table between Hijri Dates and Gregorian Dates or SQL-CLR to any library which convert Hijri to Gregorian and vice versa correctly. 

    Regarding raising this issue to Microsoft, where could I do that, and thanks a lot again.


    Saturday, February 10, 2018 12:09 PM
  • what I would suggest, is that Microfot to add a new data type called HijriDateTime (in order to make use of all other datetime functions). and this HijriDateTime accept 'yyyy-02-30' and don't accept 'yyyy-MM-31' since maximum Hijri month length is 30 days, there is no Hijri month with 31 days such as Gregorian months, 'currently I am buildg a new DWH and I am using Char(10) to store Hijri Dates'
    Saturday, February 10, 2018 12:21 PM
  • For the past 10 years or so, the place to file issues for SQL Server was through a site called Microsoft Connect, but it recently was ported to this site as part of the platform hosting feedback for most/all products:

    https://feedback.azure.com/forums/908035-sql-server

    Thanks,
    Sam Lester (MSFT)


    https://blogs.msdn.microsoft.com/samlester/

    Twitter - @SQLSamLester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, February 10, 2018 12:39 PM
    Moderator
  • Saturday, February 10, 2018 3:12 PM
  • Hi every body, after 5 hours thinking, I have developed a work-around which completely solves the issue, but unfortunately I have detected another problem, first I will show you my work-around which is a user-defined scalar-valued Function [dbo].[HijriToGregorian], which receives Char(10) as a Hijri Date, and returns its corresponding Gregorian Date, it simply check whether Hijri Date is like 29/02/yyyy or 30/02/yyyy and then replaces it with 28/02/yyyy and then converts this Hijri date to Gregorian date and finally adds 1 day or 2 days respectively to the resulting Gregorian date, here is the script :

    CREATE FUNCTION [dbo].[HijriToGregorian]
    (
    @HijriDate CHAR(10) -- IT RECEIVE DATE DATA TYPE IN THIS FORMAT '1434-02-29' Or '1434/02/29'
    )
    RETURNS DATE
    AS
    BEGIN
    DECLARE @GregDate Date;
    SET @HijriDate = SUBSTRING(@HijriDate,9,2)+'/'+SUBSTRING(@HijriDate,6,2)+'/'+SUBSTRING(@HijriDate,1,4); --To Convert from '1434-02-29' format to '29/10/1436' format

    IF NOT (@HijriDate LIKE'30/02/____' OR @HijriDate LIKE'29/02/____')
    BEGIN
    SET @GregDate = CONVERT( DATE,@HijriDate,131);
    END;

    IF @HijriDate LIKE'29/02/____'
    BEGIN
    SET @HijriDate = REPLACE(@HijriDate,'29/02/','28/02/');
    SET @GregDate = DATEADD(DAY, 1 , CONVERT( DATE,@HijriDate,131));
    END;

    IF @HijriDate LIKE'30/02/____'
    BEGIN
    SET @HijriDate = REPLACE(@HijriDate,'30/02/','28/02/');
    SET @GregDate = DATEADD(DAY, 2 , CONVERT( DATE,@HijriDate,131));
    END;

    RETURN @GregDate;
    END;
    GO

    --------

    Now I will show you the results of this function and at the end I will show you the other problem which I have detected, first of all let us show some series of Gregorian Dates and their corresponding Hijri Dates

    SELECT  
    gd AS GregDate
    ,format (Cast(gd as Date),'yyyy-MM-dd', 'ar-SA') As CorrespondingHijriDate
    FROM (values
     ('2013-01-09')  --1434-02-27
    ,('2013-01-10')  --1434-02-28
    ,('2013-01-11') --1434-02-29
    ,('2013-01-12') --1434-02-30
    ,('2013-01-13') --1434-03-01
    ,('2013-01-14') --1434-03-02
    ,('2013-01-15') --1434-03-03
    ) Series(gd);

    SELECT 
    hd AS HijriDate
    ,[dbo].[HijriToGregorian](hd) AS CorrespondingGregorianDate
     FROM (values
     ('1434-02-27')  --2013-01-09
    ,('1434-02-28') --2013-01-10
    ,('1434-02-29') --2013-01-11
    ,('1434-02-30') --2013-01-12
    ,('1434-03-01') --2013-01-12
    ,('1434-03-02') --2013-01-13
    ,('1434-03-03') --2013-01-14
    ) Series(hd);

    From the above results you can figure-out the problem, which is: if Convert(HijriDate) = x, then Format(x) should = HijriDate, but this is not always true, there are always a lag or lead by 1 Day.






    Sunday, February 11, 2018 9:05 AM
  • Here is my Final [dbo].[HijriToGregorian] which Handels till 2 Days lag / lead:

    ALTER FUNCTION [dbo].[HijriToGregorian]
    (
    @HijriDate CHAR(10) -- IT RECEIVE DATE DATA TYPE IN THIS FORMAT '1434-02-29' Or '1434/02/29'
    )
    RETURNS DATE
    AS
    BEGIN
    DECLARE @GregDate Date;
    DECLARE @InitialHijriDate CHAR(10) = REPLACE(@HijriDate,'/','-');

    SET @HijriDate = SUBSTRING(@HijriDate,9,2)+'/'+SUBSTRING(@HijriDate,6,2)+'/'+SUBSTRING(@HijriDate,1,4); --To Convert from '1434-02-29' format to '29/10/1436' format


    IF NOT (@HijriDate LIKE'30/02/____' OR @HijriDate LIKE'29/02/____')
    BEGIN
    SET @GregDate = CONVERT( DATE,@HijriDate,131);
    END;

    IF @HijriDate LIKE'29/02/____'
    BEGIN
    SET @HijriDate = REPLACE(@HijriDate,'29/02/','28/02/');
    SET @GregDate = DATEADD(DAY, 1 , CONVERT( DATE,@HijriDate,131));
    END;

    IF @HijriDate LIKE'30/02/____'
    BEGIN
    SET @HijriDate = REPLACE(@HijriDate,'30/02/','28/02/');
    SET @GregDate = DATEADD(DAY, 2 , CONVERT( DATE,@HijriDate,131));
    END;


    DECLARE @CounterHijriDate CHAR(10);
    SET @CounterHijriDate = FORMAT(@GregDate,'yyyy-MM-dd', 'ar-SA');

    --Handel 1 Day lag:
    IF ( @CounterHijriDate > @InitialHijriDate)
    BEGIN
    SET @GregDate = DATEADD(DAY,-1, @GregDate)
    END;
    --Handel 1 Day lead
    IF ( @CounterHijriDate < @InitialHijriDate)
    BEGIN
    SET @GregDate = DATEADD(DAY,1, @GregDate)
    END;

    -----2 Days lag/lead

    SET @CounterHijriDate = FORMAT(@GregDate,'yyyy-MM-dd', 'ar-SA');

    --Handel 2 Days lag:
    IF ( @CounterHijriDate > @InitialHijriDate)
    BEGIN
    SET @GregDate = DATEADD(DAY,-1, @GregDate)
    END;
    --Handel 2 Days lead
    IF ( @CounterHijriDate < @InitialHijriDate)
    BEGIN
    SET @GregDate = DATEADD(DAY,1, @GregDate)
    END;

    RETURN @GregDate;
    END;
    GO


    Sunday, February 11, 2018 11:40 AM
  • and here are the final outputs:

    SELECT  
    gd AS GregDate
    ,format (Cast(gd as Date),'yyyy-MM-dd', 'ar-SA') As CorrespondingHijriDate
    FROM (values
     ('2013-01-09')  --1434-02-27
    ,('2013-01-10')  --1434-02-28
    ,('2013-01-11') --1434-02-29
    ,('2013-01-12') --1434-02-30
    ,('2013-01-13') --1434-03-01
    ,('2013-01-14') --1434-03-02
    ,('2013-01-15') --1434-03-03
    ) Series(gd);

    SELECT 
    hd AS HijriDate
    ,[dbo].[HijriToGregorian](hd) AS CorrespondingGregorianDate
     FROM (values
     ('1434-02-27')  --2013-01-09
    ,('1434-02-28') --2013-01-10
    ,('1434-02-29') --2013-01-11
    ,('1434-02-30') --2013-01-12
    ,('1434-03-01') --2013-01-13
    ,('1434-03-02') --2013-01-14
    ,('1434-03-03') --2013-01-15
    ) Series(hd);

    Sunday, February 11, 2018 11:43 AM
  • Good day Mohamed,

    You can spend time on searching the correct algorithm to convert the data or simply choose the "DBA solution" ;-)

    Before a solution that can fit you (and brings great performance), let me comment that your current function is not working well and I am not talking only about wrong values (+1 to the value) that it returns.

    You focused on specific dates and got a conclusion out of these small number of dates that you tested, instead of testing all dates which is simple to do.

    For example please try to execute your function on the value '1438-04-30'. The function return error.

    In fact you can try these inputs and all return the same error:

    '1318-04-30'
    '1318-06-30'
    '1318-08-30'
    '1438-04-30'

    This is only a small sample of dates which will return the same error for the same reason. You can notice what these values have in common, and fix the function according to this by adding simple rule that deal with the day 30 on a double months

    but why not choose a different simple DBA approach- Using a calendar table

    In production in most cases it is HIGHLY recommended to have a numbers table and/or a calendar table, especially if you are working with dates. A calendar table can improve queries dramatically.

    Usually, a calendar table will includes information like vacations, holidays, and so on according to your system needs. In your case you can simply have a column with the Hijri date, and use this table in order to create function or simply use it direcly

    Here is a simple implementation:

    1. First we will create Numbers table (if you don't want to have Numbers table then you can create the calendar table directly, but this another accessories table that in most cases good to have) 

    DROP TABLE if exists NumbersTbl;
    DECLARE @RunDate Date
    SELECT TOP 100000 IDENTITY(int,1,1) AS Number
        INTO NumbersTbl
        FROM sys.all_objects s1
        CROSS JOIN sys.all_objects s2
    ALTER TABLE NumbersTbl ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
    GO

    Now we can use the Numbers table in order to create our calendar table (demonstrate only of the part related to the Hijri and Gregorian dates)

    DROP TABLE IF EXISTS Gregorian_Hijri_Tbl;
    select top 100000
    	DATEADD(day,0,Number)+100 as Gregorian,
    	ISNULL(CONVERT(CHAR(10), FORMAT (DATEADD(day,0,Number)+100, 'yyyy-MM-dd', 'ar-SA' )),0) as Hijri
    into Gregorian_Hijri_Tbl
    from NumbersTbl
    where FORMAT (DATEADD(day,0,Number)+100, 'yyyy-MM-dd', 'ar-SA' ) is not null
    GO-- (64850 rows affected)
    
    ALTER TABLE Gregorian_Hijri_Tbl 
    	ADD CONSTRAINT PK_Gregorian_Hijri PRIMARY KEY CLUSTERED (Hijri)
    GO

    And now if needed you can create simple function:

    DROP FUNCTION IF EXISTS [dbo].[HijriToGregorian];
    GO
    CREATE FUNCTION [dbo].[HijriToGregorian](@HijriDate CHAR(10))
    RETURNS DATE AS BEGIN
    
    	DECLARE @GregDate Date;
    	SELECT  @GregDate = Gregorian FROM Gregorian_Hijri_Tbl WHERE Hijri = @HijriDate
    
    	RETURN @GregDate;
    END;
    GO

    That is all the story :-)

    I hope this solve your need and was helpful

    * Working with Hijri date can be very challenging, and there more that can be said on the topic. I will try to find the time to write it all in blog post if I will not forget :-)

    * By the way, the reason that FORMAT function works well with Hijri dates, is based on the fact that FORMAT function in SQL Server is based on .Net, and fortunately .Net has built in HijriCalendar Class.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]





    Sunday, February 11, 2018 3:41 PM
    Moderator
  • Many Thanks for your valuable help, really appreciated, Calendar Table is really very fast when it comes to performance, but still I will try to debug the bug you have detected on my earlier function and if I fixed it, I will use it otherwise I will use your proposed calendar table. The main reasons behind my decision are: First one is that Calendar Table will not cover all range of dates, The second and the most important reason is that Hijri calendar is not fixed and depends on Lunar month investigation (especially Month9 "Ramadan" and Month10 "Shawal") so these months are sometimes 29 day, and sometimes 30 day so there is no fixed rule, so this is why I don't prefer static Calendar Table. so if I choose to use Calendar table, then I have to put Calendar table re-calibration overhead on my consideration.
    Sunday, February 11, 2018 4:56 PM
  • Many Thanks for your valuable help, really appreciated, Calendar Table is really very fast when it comes to performance, but still I will try to debug the bug you have detected on my earlier function and if I fixed it, I will use it otherwise I will use your proposed calendar table. The main reasons behind my decision are: First one is that Calendar Table will not cover all range of dates, The second and the most important reason is that Hijri calendar is not fixed and depends on Lunar month investigation (especially Month9 "Ramadan" and Month10 "Shawal") so these months are sometimes 29 day, and sometimes 30 day so there is no fixed rule, so this is why I don't prefer static Calendar Table. so if I choose to use Calendar table, then I have to put Calendar table re-calibration overhead on my consideration.

    Hi,

    >> Many Thanks for your valuable help

    You are most welcome

    >> Calendar Table will not cover all range of dates

    Why not?!?

    You should first know that FORMAT function cover only specific range of dates. This is part of the points I said that I should write about :-) There is more on the topic...

    Moreover, Calendar Table is not limited to number of rows and can cover more and not less!

    In fact this is a reason to move to use Calendar Table over built in function in this case

    >> the second and the most important reason is that Hijri calendar is not fixed

    This is not correct!
    Even if you do not familiar with the rules there are clear rules regarding when "Ramadan" comes and the same with any other holiday. Do you think that each person choose the date by himself? How are the dates selected?!? the answer is by clear rules.

    * in the old days before the modern calendar, when people control smaller areas the date of holidays usually decided by one central place. For example in the Judaism, the dates were always decided in Jerusalem who was the capital city, and all other places waited for the announcement from Jerusalem about the starting of the holiday (this is why in the old days far places celebrated several days after sometimes). Today we all works with calendars which we can create (calculate) in advance for hundred of years , and therefore we can all start the holiday on the same time according to the time-zone we are and not according to the time it take to pass the announcement  from one central place to all the world using fire (in the old fire was used to pass the message) and horses.

    "You cannot change the history but only the stories about the history" as one smart person used to say. Therefore, at least you can add the historical dates and be sure these are correct.

    1. You can control what values you insert to the table and make sure these fit your needs. This is a one time job

    2. There is no need to do this one time job, since Micrsooft did it for you. DO NOT USE CODE THAT YOU DID NOT READ AND UNDERSTOOD.

    my table is based on the same "FORMAT" function as your function, and it take into considerations all the parameters. As I said... someone did the work for you - Microsoft

    >> especially Month9 "Ramadan" and Month10 "Shawal"... so there is no fixed rule

    This is the advantage for using table over trying to find the rule and create function based on algorithm :-)

    Even if there was no rule (which is not correct and there are rules which Microsoft probably took into conciliation when they developed their function), then in table you can simply control the values directly even if there is no logic and rules.

    >>  don't prefer static Calendar Table

    This static table created by you and you can control the content, while using FORMAT, CONVERT, or any other built in function, you have no control on the result (Microsoft have). While you create the table you can control any role and any value which does not fit the rule, and you have the option to change it in the future as well simply by updating the table.

    Everything that you said till now only show advantages of using table over function :-)

    But with that being said, this is your call, and I am not familiar with your system


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, February 11, 2018 5:39 PM
    Moderator
  • >Thanks for the clarification on how holidays is get appointed in Judaism, However in Islam they are using (somehow a way similar to the old Judaism way) so each country has its own committee which is responsible for investigating the vision of Shawal and Ramadan crescent, so if you asked me what it is Gregorian date pertaining to 1st of Ramadan previous year (or any other past year) I will say it is (2017-05-27) but if you asked me what it is Gregorian date pertaining to 1st of Ramadan this year, next year (or any other future year) I will say I don’t know but it will be either (2018-05-16) or (2018-05-15) depending on committee decision (whether they will be able to see the crescent or not), so in summary, the mapping between Hijri and Gregorian Dates is fixed and guaranteed in History, and is forecasted in future and subjected to change.

    >Why I want to rely on Microsoft Format() function (please correct me if I am wrong, and forgive me because I am depending on assumption, and I will try to avoid USING CODE THAT I DID NOT READ AND UNDERSTOOD ) I think Microsoft has its own internal mapping table between Hijri Date and Gregorian date, and it updates this table on regular basis, since all conversions in History are somehow correct.

    >Based on your detailed (Thankful and Highly Appreciated) clarification, it is clear that using mapping table is more convenient, but other reason why I don’t prefer mapping table is that it will help me this time only, so what about next time, and what about if any other person using Microsoft SQL Server tries to convert Hijri to Greg? So I think Microsoft should provide a reasonable help.

    >Regarding the bug in my previous user-defined function, again it is due to Convert() function itself.

    select CONVERT( DATE,'30/06/1318',131); -- Conversion failed when converting date and/or time from character string.

    select CONVERT( DATE,'30/04/1438',131); -- Conversion failed when converting date and/or time from character string.




    Sunday, February 11, 2018 7:43 PM
  • here is fixed function (but not tested for wide range of dates yet):

    ALTER FUNCTION [dbo].[HijriToGregorian] -- SELECT [dbo].[HijriToGregorian]('1435-02-30')
    (
    @HijriDate CHAR(10) -- IT RECEIVE DATE DATA TYPE IN THIS FORMAT '1434-02-29' Or '1434/02/29'
    )
    RETURNS DATE
    AS
    BEGIN
    	DECLARE @GregDate Date;
    	DECLARE @InitialHijriDate CHAR(10) = REPLACE(@HijriDate,'/','-');
    
    	SET @HijriDate = SUBSTRING(@HijriDate,9,2)+'/'+SUBSTRING(@HijriDate,6,2)+'/'+SUBSTRING(@HijriDate,1,4); --To Convert from '1434-02-29' format to '29/10/1436' format
    	
    
    	IF NOT (@HijriDate LIKE'30/__/____' OR @HijriDate LIKE'29/02/____')
    	BEGIN
    		SET @GregDate = CONVERT( DATE,@HijriDate,131);
    	END;
    	
    	IF @HijriDate LIKE'29/02/____'
    	BEGIN
    		SET @HijriDate = REPLACE(@HijriDate,'29/02/','28/02/');
    		SET @GregDate = DATEADD(DAY, 1 , CONVERT( DATE,@HijriDate,131));
    	END;
    
    	IF @HijriDate LIKE'30/__/____'
    	BEGIN
    		SET @HijriDate = REPLACE(@HijriDate,'30/','28/');
    		SET @GregDate = DATEADD(DAY, 2 , CONVERT( DATE,@HijriDate,131));
    	END;
    	
    	
    	DECLARE @CounterHijriDate CHAR(10);
    	SET @CounterHijriDate = FORMAT(@GregDate,'yyyy-MM-dd', 'ar-SA');
    	
    	--Handel 1 Day lag:
    	IF ( @CounterHijriDate > @InitialHijriDate)
    	BEGIN
    		SET @GregDate = DATEADD(DAY,-1, @GregDate)
    	END;
    	--Handel 1 Day lead
    	IF ( @CounterHijriDate < @InitialHijriDate)
    	BEGIN
    		SET @GregDate = DATEADD(DAY,1, @GregDate)
    	END;
    
    	-----2 Days lag/lead
    
    	SET @CounterHijriDate = FORMAT(@GregDate,'yyyy-MM-dd', 'ar-SA');
    	
    	--Handel 2 Days lag:
    	IF ( @CounterHijriDate > @InitialHijriDate)
    	BEGIN
    		SET @GregDate = DATEADD(DAY,-1, @GregDate)
    	END;
    	--Handel 2 Days lead
    	IF ( @CounterHijriDate < @InitialHijriDate)
    	BEGIN
    		SET @GregDate = DATEADD(DAY,1, @GregDate)
    	END;
    
    	RETURN @GregDate;
    END;
    GO
    

    Sunday, February 11, 2018 7:59 PM
  • I have finished testing my function after Bug fixing, here is the script which I have used for testing:

    select Hijri
    , Cast(Gregorian as Date) Gregorian 
    , dbo.HijriToGregorian(Hijri) CalculatedGreg 
    From Gregorian_Hijri_Tbl
    where dbo.HijriToGregorian(Hijri) <> Gregorian
    
    --(0 row(s) affected)

    it works fine, but it is 4 times slower than Ronen's function (which is based on Gregorian_Hijri_Tbl populated with 64850 rows right now).



    Monday, February 12, 2018 10:18 AM
  • After closing the Hijri to Gregorian conversion issue (with two alternatives), another issue appeared, which is that, we can’t use the handy and ready-made TSQL datetime functions with Hijri Dates, so the journey of finding a suitable work-arounds has just started.

    So I have to develop a Hijri scalar-valued functions similar to the following Gregorian functions:

    I will start with the work-around for HijriDayOfYearId, so for GregorianDayOfYearId we can use the handy ready-made DayOfYear DatePart function as follow:

    CREATE FUNCTION [dbo].[GetGregDayOfYearId] -- SELECT dbo.GetGregDayOfYearId('2578-07-05','G') FF
    (
    @Date CHAR(10)
    ,@Type CHAR(1)='G'
    )
    RETURNS SMALLINT --MAX VALUE IS 366
    AS
    BEGIN
    	IF UPPER(@Type)='H'
    	BEGIN
    		SET @Date = [dbo].[HijriToGregorian](@Date);
    	END;
    	RETURN DatePart(DAYOFYEAR, @Date);
    END;
    
    GO

    But for HijriDayOfYearId we can use the following work-around:

    CREATE FUNCTION [dbo].[GetHijriDayOfYearId] -- SELECT dbo.GetHijriDayOfYearId('1318-02-30','h') FF
    (
    @Date CHAR(10)
    ,@Type CHAR(1)='G'  -- it receives either G=gregorian date or H=Hijri date and returns its corresponding HijriDayOfYearId.
    )
    RETURNS SMALLINT --Maximum Possible value should be less than or equal to 355 "Hijri Year Length is either 354 or 355 day".
    AS
    BEGIN
    	DECLARE @HijriDayOfYearId SMALLINT = 0;
    
    	-----Test Valid Calendar Type----
    	IF UPPER(@Type) NOT IN ('G','H')
    	BEGIN
    		RETURN CAST('  Date Calendar @Type Should be either (''G'' or ''H''). ''' + @Type + ''' is Invalid. ' AS INT); --There is no simple way to raise error outside UDF other than this.
    	END;
    
    	IF UPPER(@Type) = 'H' 
    	BEGIN
    		SET @Date = CAST([dbo].[HijriToGregorian](@Date) AS CHAR(10)); --This will Check if the input Hijri Date is a valid Hijri Date as well.
    	END;
    
    	DECLARE @HijriDate CHAR(10) = [dbo].[GregorianToHijri](@Date); -- like '1436-09-15'
    	DECLARE @FirstHijriDate CHAR(10) = LEFT(@HijriDate,4)+'-01-01'; -- like '1436-01-01'
    	DECLARE @FirstHijriDateGregDate Date = [dbo].[HijriToGregorian](@FirstHijriDate);
    	SET @HijriDayOfYearId = DATEDIFF(DAY , @FirstHijriDateGregDate , @Date) + 1;
    
    	RETURN @HijriDayOfYearId;
    END;
    GO

    • Edited by Mohamed Almogtaba Ali Thursday, February 15, 2018 12:00 PM Add Test Valid Calendar Type Feature
    Monday, February 12, 2018 11:55 AM
  • I have finished testing my function after Bug fixing, here is the script which I have used for testing:

    select Hijri
    , Cast(Gregorian as Date) Gregorian 
    , dbo.HijriToGregorian(Hijri) CalculatedGreg 
    From Gregorian_Hijri_Tbl
    where dbo.HijriToGregorian(Hijri) <> Gregorian
    
    --(0 row(s) affected)

    it works fine, but it is 4 times slower than Ronen's function (which is based on Gregorian_Hijri_Tbl populated with 64850 rows right now).

    Hi,

    So basically, you confirm that the table gives you good values :-)
    I am kidding, this is good way to compare two option and confirm both return the same result

    but let go back to your previous message :-)

    >> the mapping between Hijri and Gregorian Dates is fixed and guaranteed in History, and is forecasted in future and subjected to change

    Interesting :-)

    Can you give us one time (a date) that it happened between the year 2012 and today?

    >> I think Microsoft has its own internal mapping table between Hijri Date and Gregorian date

    Yes they do, and in my solution I used this information to fill the table :-)

    * The information included in the FORMAT function

    >> and it updates this table on regular basis, since all conversions in History are somehow correct

    Well, the discussion become more Interesting ;-)

    I am sure that if there was a change then it is updated, but the same function can be executed on old version from 2012 to 2017, which mean that if there was a change in the date of the Ramadan, between 2012 and today then old servers should give wrong data. Do you know if the date was change during this time?

    Can you give us one time (a date) that it happened between the year 2012 and today?

    If there was a change then we can confirm the issue :-)

    >> it will help me this time only, so what about next time

    Same as in function. You will need to update the function or the table. Should simpler to execute simple update query in the table, then find the new algorithm to cover the change each time (in my opinion)

    >> and what about if any other person using Microsoft SQL Server tries to convert Hijri to Greg?

    Same as function. Instead of give someone the code for the function, you can give him the code to create the table

    >> So I think Microsoft should provide a reasonable help.

    I AGREE :-)

    opening the connect call was good idea. 

    * Please check if there was change in the date during the last several years. I am interesting to cover this point to my personal knowledge as well. I am pretty sure that there was no change in the function from 2012 to today (regarding this point).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, February 12, 2018 12:19 PM
    Moderator
  • Hi Ronen,

    So basically, you confirm that the table gives you good values :-)

    Yes I confirm it gives good values so far, but I can’t guarantee it will give correct results after X years ahead (unless you add a logic to regularly sync Gregorian_Hijri_Tbl with result obtained from Format function, since if a change occurred then Microsoft will updates its internal Format function table, but your table will be out-of-date), but if you choose to use my function then you have to bear the cost of low performance in order to overcome table re-calibration overhead.

    Can you give us one time (a date) that it happened between the year 2012 and today?

    Fortunately I couldn’t find any date mismatch between what was expected and the actual dates (in the period 2012 till 2017).

    Here is what I have got depending on Format() function, which I have later cross-checked with results obtained from other authentic Hijri to Gregorian conversion websites.

    SELECT

    hd AS HijriDate

    ,[dbo].[HijriToGregorian](hd) AS CorrespondingGregorianDate

     FROM (values

     ('1433-09-01') --2012-07-20

    ,('1434-09-01') --2013-07-09

    ,('1435-09-01') --2014-06-28

    ,('1436-09-01') --2015-06-18

    ,('1437-09-01') --2016-06-06

    ,('1438-09-01') --2017-05-27

    ) Series(hd);

    This result means it is either quite a coincidence or the algorithm which is used to calculate Hijri Date is too accurate (since 2012 till 2017 is not a short period, and crescent vision investigation is held twice a year at minimum).

    However the problem still valid since appointing Hijri Date depends on the culture of the country as well , so, some countries such as Morocco adopts crescent vision investigation only (not only for Ramadan and Shawal but for all other Hijri months), but other countries such as Saudi Arabia adopts hybrid approach (both astronomical (i.e. algorithmic) and crescent vision investigation), so if it is impossible to see the crescent astronomically then KSA will ignore crescent vision investigation result, whereas, if it is possible to see the crescent astronomically then it will accept crescent vision investigation result whatever its (i.e. still there is a chance that Hijri Date in KSA may not comply with what was expected according to algorithm if the investigation committee were not able to see the crescent when it is possible to see it astronomically). So appointing the first of Ramadan and Shawal is too thorny and complex and there are lots of details…. Here are some cases which show that Hijri date depends on culture:

    1420-09-01: Saudi Arabia: 1999-12-09

    1420-09-01: Yemen: 1999-12-08

    ----

    1424-09-01: Saudi Arabia: 2003-10-27      

    1424-09-01: Yemen, Jourdan, Egypt: 2003-10-26

    By the way    select Format(cast('2003-10-26' as date), 'yyyy-MM-dd', 'ar-SA') returns 1424-09-01 which is incorrect (another point supports your table approach).





    Tuesday, February 13, 2018 12:20 PM
  • Hi Everybody,

    I have detected another bug on my dbo.HijriToGregorian, which is that it simply doesn’t check whether the input Hijri date is a valid Hijri Date or not, here is a sample:

    SELECT dbo.HijriToGregorian('1318-01-30') --it should return an error stating that there is no such Hijri Date since last date was 1318-01-29

    --but actually it returns 1900-05-29 which is completely wrong

    SELECT dbo.HijriToGregorian('1318-01-30')

    --Ronen's function returns Null which is accepted.

    So, I have resolved the bug and enhanced the performance of the function by recalibrating leading before lagging since it is more probable, and by checking the equality directly after +1 day leading recalibration, since +2 days is too rare, Therefor, after these modifications my function is now 3 instead of 4 times slower than Ronen’s function.

    Here is the modified version:

    ALTER FUNCTION [dbo].[HijriToGregorian] -- SELECT [dbo].[HijriToGregorian]('1439-09-01') FF  
    (
    @HijriDate CHAR(10) -- IT RECEIVE DATE DATA TYPE IN THIS FORMAT '1434-02-29' Or '1434/02/29'
    )
    RETURNS DATE
    AS
    BEGIN
    	DECLARE @GregDate Date;
    	DECLARE @InitialHijriDate CHAR(10) = REPLACE(@HijriDate,'/','-');
    
    	SET @HijriDate = SUBSTRING(@HijriDate,9,2)+'/'+SUBSTRING(@HijriDate,6,2)+'/'+SUBSTRING(@HijriDate,1,4); --To Convert from '1434-02-29' format to '29/02/1434' format
    	
    
    	IF NOT (@HijriDate LIKE'30/__/____' OR @HijriDate LIKE'29/02/____')
    	BEGIN
    		SET @GregDate = CONVERT( DATE,@HijriDate,131);
    	END;
    	
    	IF @HijriDate LIKE'29/02/____'
    	BEGIN
    		SET @HijriDate = REPLACE(@HijriDate,'29/02/','28/02/');
    		SET @GregDate = DATEADD(DAY, 1 , CONVERT( DATE,@HijriDate,131));
    	END;
    
    	IF @HijriDate LIKE'30/__/____'
    	BEGIN
    		SET @HijriDate = REPLACE(@HijriDate,'30/','28/');
    		SET @GregDate = DATEADD(DAY, 2 , CONVERT( DATE,@HijriDate,131));
    	END;
    	
    	-----Handle Lag/Lead----
    	DECLARE @CounterHijriDate CHAR(10);
    	SET @CounterHijriDate = FORMAT(@GregDate,'yyyy-MM-dd', 'ar-SA');
    
    		--Handle 1 Day lead
    	IF ( @CounterHijriDate < @InitialHijriDate)
    	BEGIN
    		SET @GregDate = DATEADD(DAY,1, @GregDate)
    		SET @CounterHijriDate = FORMAT(@GregDate,'yyyy-MM-dd', 'ar-SA');
    	END;
    
    	IF @CounterHijriDate = @InitialHijriDate
    	BEGIN
    		RETURN @GregDate;
    	END;
    
    	--Handle 2 Days lead
    	IF ( @CounterHijriDate < @InitialHijriDate)
    	BEGIN
    		SET @GregDate = DATEADD(DAY,1, @GregDate)
    		SET @CounterHijriDate = FORMAT(@GregDate,'yyyy-MM-dd', 'ar-SA');
    	END;
    
    	
    	--Handle 1 Day lag:
    	IF ( @CounterHijriDate > @InitialHijriDate)
    	BEGIN
    		SET @GregDate = DATEADD(DAY,-1, @GregDate)
    		SET @CounterHijriDate = FORMAT(@GregDate,'yyyy-MM-dd', 'ar-SA');
    	END;
    		
    	--Handle 2 Days lag:
    	IF ( @CounterHijriDate > @InitialHijriDate)
    	BEGIN
    		SET @GregDate = DATEADD(DAY,-1, @GregDate)
    		SET @CounterHijriDate = FORMAT(@GregDate,'yyyy-MM-dd', 'ar-SA');
    	END;
    
    
    	IF @CounterHijriDate = @InitialHijriDate
    	BEGIN
    		RETURN @GregDate;
    	END;
    	
    	IF @InitialHijriDate BETWEEN '1318-01-01' /*MinHijriDate*/ AND '1500-12-30' /*MaxHijriDate*/ --i.e. @InitialHijriDate is Recalibratable
    	BEGIN
    		RETURN Cast(CAST('  There is no such Hijri Date ''' + @InitialHijriDate + '''.  ' AS INT) AS CHAR(10));
    	END;
    
    	--@InitialHijriDate is not Recalibratable ==> return whatever @GregDate you get
    	RETURN @GregDate
    
    END;
    GO

    • Edited by Mohamed Almogtaba Ali Tuesday, February 20, 2018 2:02 PM Expand Date Conversion Range to accommodate the whole Range
    Thursday, February 15, 2018 11:42 AM
  • Continue on the same topic:

    CREATE FUNCTION [dbo].[GregorianToHijri]
    (
    @GregorianDate DATE
    )
    Returns CHAR(10)
    AS
    BEGIN
    	RETURN FORMAT ( @GregorianDate, 'yyyy-MM-dd', 'ar-SA' )
    END;
    GO

    and here is the work around for Hijri Date Id:

    CREATE FUNCTION [dbo].[GetHijriDateId] -- SELECT dbo.GetHijriDateId('2013-02-30','k') FF
    (
    @Date CHAR(10) 
    ,@Type CHAR(1)='G'
    )
    Returns INT
    AS
    BEGIN
    	DECLARE @HijriDateId INT;
    
    	IF UPPER(@Type) NOT IN ('G','H')
    	BEGIN
    		RETURN CAST('  Date Calendar @Type Should be either (''G'' or ''H''). ''' + @Type + ''' is Invalid. ' AS INT); --There is no simple way to raise error outside UDF other than this.
    	END;
    
    	IF UPPER(@Type) ='H' 
    	BEGIN
    		DECLARE @TestValidHijriDate Date = dbo.HijriToGregorian(@Date); --IF converted successfully ==> it is a valid Hijri Date otherwise we will get an error, because we may receive CHAR(10) look like this '1436-17-35'
    		SET @HijriDateId =  CAST(REPLACE(REPLACE(@Date,'-',''),'/','') AS INT);
    	END;
    
    	IF UPPER(@Type) ='G' 
    	BEGIN
    		SET @HijriDateId = CAST(FORMAT (CAST(@Date AS DATE), 'yyyyMMdd', 'ar-SA' ) AS INT);
    	END;
    
    	RETURN @HijriDateId
    END;
    GO


    Work-around for Hijri Day of Month Id:

    CREATE FUNCTION [dbo].[GetHijriDayOfMonthId] -- SELECT dbo.GetHijriDayOfMonthId('2013-02-30','g') FF
    (
    @Date CHAR(10)
    ,@Type CHAR(1)='G'
    )
    Returns TINYINT --MAX VALUE SHOULD BE LESS THAN OR EQUAL 30 Days.
    AS
    BEGIN
    	-----Test Valid Calendar Type----
    	IF UPPER(@Type) NOT IN ('G','H')
    	BEGIN
    		RETURN CAST('  Date Calendar @Type Should be either (''G'' or ''H''). ''' + @Type + ''' is Invalid. ' AS INT); --There is no simple way to raise error outside UDF other than this.
    	END;
    
    	---------Test Valid Hijri Date----------
    	IF UPPER(@Type) = 'H'
    	BEGIN
    		DECLARE @TestValidHijriDate Date = dbo.HijriToGregorian(@Date); --IF converted successfully ==> it is a valid Hijri Date otherwise we will get an error, because we may receive CHAR(10) look like this '1436-17-35'
    	END;
    
    	IF UPPER(@Type) = 'G' 
    	BEGIN
    		SET @Date = [dbo].[GregorianToHijri](@Date);
    	END
    	RETURN RIGHT(@Date,2);
    END;
    GO

    Work-around for Day of Week Id:

    CREATE FUNCTION [dbo].[GetHijriDayOfWeekId] -- SELECT dbo.GetHijriDayOfWeekId('1318-02-30','h') FF
    (
    @Date CHAR(10)
    ,@Type CHAR(1)='G'
    )
    Returns INT
    AS
    BEGIN
    	-----Test Valid Calendar Type----
    	IF UPPER(@Type) NOT IN ('G','H')
    	BEGIN
    		RETURN CAST('  Date Calendar @Type Should be either (''G'' or ''H''). ''' + @Type + ''' is Invalid. ' AS INT); --There is no simple way to raise error outside UDF other than this.
    	END;
    
    	IF UPPER(@Type) = 'H' 
    	BEGIN
    		SET @Date = [dbo].[HijriToGregorian](@Date); --This will also check if @Date is a valid Hijri Date.
    	END;
    	DECLARE @Shift TINYINT = 0;
    	/* @Shift: Values Clarification:
    	6 ==> Sunday is first Day of The week.
    	5 ==> Monday is first Day of The week.
    	4 ==> Tuesday is first Day of The week.
    	3 ==> Wednesday is first Day of The week.
    	2 ==> Thursday is first Day of The week.
    	1 ==> Friday is first Day of The week.
    	0 ==> Saturday is first Day of The week.
    	*/
    	RETURN CAST(  ( DatePart(dw,Cast(@Date as Date)) + @Shift) %7 +1  AS INT)
    END;
    GO

     
    Thursday, February 15, 2018 12:13 PM