Database SQL Problem
This command is working on some SQL Server databases, but not on others.
select Cast(Cast(DateDiff(mm,'20110608','20110708') as decimal(5开发者_运维百科,2))/12 as decimal(5,2))
It either returns this error
Arithmetic overflow error converting numeric to data type numeric.
or works and returns:
0.08
Is there any settings I should change on the database?
solved it by setting:
SET NUMERIC_ROUNDABORT OFF
Different instances running the same version or different versions of SQL Server? The only thing that stands out is your date format (e.g. '20110608'). Possibly not supported in all SQL Server versions?
Anyway, try this instead and see if you get the overflow error:
SELECT
CONVERT(Decimal(5, 2), DATEDIFF(MM, '20110608', '20110708') / 12.0);
精彩评论