SQL Server 2008 R2 datetime format - 131

SQL Server 2008 R2 datetime format - 131 by Mahesh Bachina

Closed

as By Design Help for as By Design

Type:	Bug
ID:	776464
Opened:	1/14/2013 2:07:01 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

English

SQL Server 2008 R2

SQL Engine

Windows Server 2008 R2 Enterprise

English

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

3/03/1434 5:47:23:887PM

14/1/2013 5:47:23:887PM

0 attachments

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?