Home Dashboard Directory Help
Search

There is a bug in the way SQL Server 2008R2 and 2014 convert certain computed columns. by Philip Lewis


Status: 

Active


1
0
Sign in
to vote
Type: Bug
ID: 862776
Opened: 4/28/2014 5:15:28 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

SQL incorrectly converts syntax, or convert syntax returns incorrect result.
This is broken in 2008R2 and 2014

select CONVERT([char](4),replace(CONVERT([varchar](5),CONVERT([time](0),dateadd(second,526753400%(100000),'00:00:00'),0),0),':',''),0) as [UseSelectConvert],
     cast(replace(cast(cast(dateadd("S", 526753400 % 100000, '00:00:00') as time(0)) as varchar(5)),':','') as char(4)) as [UseSelectCast]

The second column is the desired result. The first column is the syntax created (and apparently used) during table creation while declaring a computed column (see code below)
Details
Sign in to post a comment.
Posted by Microsoft on 5/28/2014 at 9:21 AM
Thanks for submitting this feedback.

There was a bug in SQL Server 2008: there used to be a difference between the default for computed columns (0) and other expressions (121). This was fixed in SQL2012: the default is now always 121.
Because this was a behavior change, we fixed it under compat level 110. So, to benefit from the fix, please use compat level 110 or 120.
See also the fourth row in the section "Differences Between Lower Compatibility Levels and Levels 110 and 120" on the following page:
http://msdn.microsoft.com/en-us/library/bb510680.aspx

--
Jos de Bruijn - SQL Server PM
Sign in to post a workaround.