Converting Hijri Date

  • Hi

    I have a string which contains '8/12/1430' i want to convert it into Gregorian date. How to do this?

    Thanks in advance

    Azeem

  • Unfortunately you can’t. The year 1430 can not be used in datetime data type. If you’ll try to use it, you’ll get an out of range error.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks

  • What version of SQL Server are you using?

    In SQL 2008 you have a date data type with a range of 0001-01-01 through 9999-12-31 (January 1, 1 A.D. through December 31, 9999 A.D.).

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • I ran the following: select convert(datetime, '8/12/1430' , 131) and it returned 2009-11-25 00:00:00.000. Is that correct?

  • Lynn Pettis (2/10/2010)


    I ran the following: select convert(datetime, '8/12/1430' , 131) and it returned 2009-11-25 00:00:00.000. Is that correct?

    Unfortunately I have to admit that I was wrong. Hijri calendar is the Islamic calendar which of course has a completely different years count then the Gregorian year. Since the original date was an Hijri date, the conversion of that date resultes in a valid date (just as Lyn wrote).

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Adi Cohn-120898 (2/10/2010)


    Lynn Pettis (2/10/2010)


    I ran the following: select convert(datetime, '8/12/1430' , 131) and it returned 2009-11-25 00:00:00.000. Is that correct?

    Unfortunately I have to admit that I was wrong. Hijri calendar is the Islamic calendar which of course has a completely different years count then the Gregorian year. Since the original date was an Hijri date, the conversion of that date resultes in a valid date (just as Lyn wrote).

    Adi

    Which put me on the wrong foot as well 🙂 I suspected something like this at first (but didn't further investigate) but when I saw the OP's reaction to the first answer, it looked to me like the OP was looking for inserting the date 'AS IS'.

    😀

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • Hi

    I tried the query, its working for some values and it is giving error for others.

    It is converting '7/12/1430' into gregorian.

    where as it is giving error for '30/12/1430'.

    I am using the below query for conversion.

    Select Convert(Datetime,'30/12/1430',131) and i get an error

    Msg 242, Level 16, State 3, Line 1

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Thanks

    Azeem.

  • From what I can tell Hijri format requires that the date portion is mm/dd/yyyy. The strings that are failing to convert have a format of dd/mm/yyyy.

  • Acutely Hijri calendar has the structure of dd/mm/yyyy (you can check it in BOL at the explanation about convert function), but I think that the 12th month doesn’t have 30 days (or at least didn’t have in the year 1430). If you run this convert:

    Select Convert(Datetime,'29/12/1430',131)

    You’ll get no error

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi

    I have tried that and no errors encountered. Any suggestions as How to overcome this issue?

    Thanks

    Azeem

  • I’m not expert on the subject, but it seems to me that you got a none valid date. I think that this is just like getting the date Feb 31, 2010 (which is not a valid date). Who ever is giving you the data has to make sure that he gives you a valid data.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi

    I have used this and got no error. But how do we overcome this problem of 30 days in the 12th month. How does the sql convert the date? will you explain the processing involved.

    Thanks

    Azeem

  • The problem is that you have a none valid date. The Hijri calendar has 12 months. Each month has 30 or 29 days. Most of the years have 354 days but every 2 or three years, a day is added to the year and that year has 355 years. Only at the years that have 355 days, the 12th month has 30 days. The year 1430 had only 354 and the 12th month did not have 30 days. In short you are trying to convert a value that doesn’t represent a valid date into datetime data type. This would be just like running this statement:

    --Feburay has only 28 days at 2010, so this

    --is not a valid date

    select convert(datetime,'02/30/2010',101)

    If you want to know more about the Hijri calendar – here is a good explanation http://www.phys.uu.nl/~vgent/islam/islam_tabcal.htm%5B/url%5D

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi

    Thanks you very much for the advice and i really appreciate that.

    Thanks Again.

    Azeem

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply