Converting Hijri Date

  • Azzu

    Ten Centuries

    Points: 1363

    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

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    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/

  • Azzu

    Ten Centuries

    Points: 1363

    Thanks

  • wschampheleer

    SSCertifiable

    Points: 5504

    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]

  • Lynn Pettis

    SSC Guru

    Points: 442118

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

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    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/

  • wschampheleer

    SSCertifiable

    Points: 5504

    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]

  • Azzu

    Ten Centuries

    Points: 1363

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442118

    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.

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    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/

  • Azzu

    Ten Centuries

    Points: 1363

    Hi

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

    Thanks

    Azeem

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    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/

  • Azzu

    Ten Centuries

    Points: 1363

    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

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    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/

  • Azzu

    Ten Centuries

    Points: 1363

    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