Answered by:
ERROR Converting Data Type varchar to numeric
Question

Below is garbage data and structure (I think enough to get point across). How can I perform calculations needed?
Create Table #1234 ( abcd decimal(16,4) ,defg decimal(16,4) ,hijk decimal(16,4) ,logon datetime ,logoff datetime ) Insert Into #1234 values ('14.2323','6.1432','8.1111','11/17/2015 06:00:00.143', '11/17/2015 12:00:58.143') SELECT SUM(CAST(abcd As DECIMAL(16,4))),SUM(CAST(defgh As DECIMAL(16,4))), SUM(CAST(hijk As DECIMAL(16,4))),SUM(CAST([Reg] As DECIMAL(16,4))), SUM(CAST(rr As DECIMAL(16,4))) FROM ( Select abcd As abcd ,case when abcd >= 14.0000 then ABS(abcd)FLOOR(ABS(abcd)) else '0.0000' end As defg ,hijk As hijk ,(datediff(hh, logon,logoff)abcd) As rr FROM #1234 ) X
Tuesday, November 17, 2015 8:33 PM
Answers

A numeric does not need single quotes:
Create Table #1234 ( abcd decimal(16,4) ,defg decimal(16,4) ,hijk decimal(16,4) ,logon datetime ,logoff datetime ) Insert Into #1234 values (14.2323,6.1432,8.1111,'11/17/2015 06:00:00.143', '11/17/2015 12:00:58.143') SELECT SUM(CAST(abcd As DECIMAL(16,4))),SUM(CAST(defg As DECIMAL(16,4))) , SUM(CAST(hijk As DECIMAL(16,4))) ,SUM(CAST([Reg] As DECIMAL(16,4))) , SUM(CAST(rr As DECIMAL(16,4))) FROM ( Select abcd As abcd ,case when abcd >= 14.0000 then ABS(abcd)FLOOR(ABS(abcd)) else 0.0 end As defg ,hijk As hijk ,(datediff(hh, logon,logoff)abcd) As rr FROM #1234 ) X drop table #1234
 Marked as answer by Allweknowiswhatwearetold Tuesday, November 17, 2015 8:57 PM
Tuesday, November 17, 2015 8:49 PM
All replies

Here is the wery well explanation regarding your issue — Handling error converting data type varchar to numeric in SQL Server
 Proposed as answer by Vero4ka Wednesday, November 18, 2015 8:45 AM
Tuesday, November 17, 2015 8:46 PM 
A numeric does not need single quotes:
Create Table #1234 ( abcd decimal(16,4) ,defg decimal(16,4) ,hijk decimal(16,4) ,logon datetime ,logoff datetime ) Insert Into #1234 values (14.2323,6.1432,8.1111,'11/17/2015 06:00:00.143', '11/17/2015 12:00:58.143') SELECT SUM(CAST(abcd As DECIMAL(16,4))),SUM(CAST(defg As DECIMAL(16,4))) , SUM(CAST(hijk As DECIMAL(16,4))) ,SUM(CAST([Reg] As DECIMAL(16,4))) , SUM(CAST(rr As DECIMAL(16,4))) FROM ( Select abcd As abcd ,case when abcd >= 14.0000 then ABS(abcd)FLOOR(ABS(abcd)) else 0.0 end As defg ,hijk As hijk ,(datediff(hh, logon,logoff)abcd) As rr FROM #1234 ) X drop table #1234
 Marked as answer by Allweknowiswhatwearetold Tuesday, November 17, 2015 8:57 PM
Tuesday, November 17, 2015 8:49 PM