locked
ERROR Converting Data Type varchar to numeric RRS feed

  • 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

    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

    Tuesday, November 17, 2015 8:49 PM