February 10, 2010 at 4:50 am
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
February 10, 2010 at 5:00 am
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/
February 10, 2010 at 5:03 am
Thanks
February 10, 2010 at 5:15 am
February 10, 2010 at 6:37 am
I ran the following: select convert(datetime, '8/12/1430' , 131) and it returned 2009-11-25 00:00:00.000. Is that correct?
February 10, 2010 at 7:03 am
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/
February 10, 2010 at 7:07 am
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'.
😀
February 10, 2010 at 10:52 pm
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.
February 10, 2010 at 11:14 pm
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.
February 10, 2010 at 11:35 pm
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/
February 10, 2010 at 11:49 pm
Hi
I have tried that and no errors encountered. Any suggestions as How to overcome this issue?
Thanks
Azeem
February 11, 2010 at 12:03 am
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/
February 11, 2010 at 12:10 am
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
February 11, 2010 at 1:55 am
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/
February 11, 2010 at 2:46 am
Hi
Thanks you very much for the advice and i really appreciate that.
Thanks Again.
Azeem
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply