SQL Server 2008 R2 datetime format - 131 - by Mahesh Bachina

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 776464 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 1/14/2013 2:07:01 AM
Access Restriction Public



Following query returns wrong results in SQL server 2008 R2. Eventhough my server datetime is 14-Jan-2013, below query returns 3/03/1434.


 3/03/1434  5:47:23:887PM
Sign in to post a comment.
Posted by Umachandar [MSFT] on 1/15/2013 at 11:45 AM
Hello Mahesh,
The style 131 uses the Hijri calendar to perform the conversion from the gregorian date. So the behavior is by design.

Umachandar, SQL Programmability Team
Posted by Scott R. 007 on 1/14/2013 at 2:26 PM
FYI - the time part returned from using format code 131 in the CONVERT function appears to be correct, but the returned date part is not the same as the traditional calendar system (as noted above).

Books online notes that format code 131 uses the Hijri calendar system and the Kuwaiti algorithm, suggesting that a returned date different that the traditional calendar system may be the expected result. I don't know the Hirji calendar system well enough to say if this different returned date is correct or not.

More importantly, the returned date results are consistent across all recent versions of SQL Server (2012, 2008 R2, 2008, 2005, 2000), and not just SQL 2008 R2 as noted above. I tested against these different versions using SSMS from SQL 2008 R2. So the result are either all correct or all incorrect.

Bug or feature?