Home Dashboard Directory Help
Search

SQL Server 2008 R2 datetime format - 131 by Mahesh Bachina


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 776464
Opened: 1/14/2013 2:07:01 AM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

Hi,

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

Query:
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)

Result:
3/03/1434 5:47:23:887PM
Details
Sign in to post a comment.
Posted by Microsoft 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?
Sign in to post a workaround.