needing help casting varchar to decimal

Question

needing help casting varchar to decimal

SSCarpal Tunnel

Points: 4298

September 23, 2021 at 3:24 pm

I'm needing to convert some varchar figures to decimal for sums, but can't quite find an example that works... the second block of code just returns the list of data without cast or sum:

SELECT
       SUM(CAST([Convo] AS DECIMAL(10,2)))
      ,SUM(CAST([F9D2]) AS DECIMAL(10,2)))
      ,SUM(CAST([F9D5]) AS DECIMAL(10,2)))
      ,SUM(CAST([F9DMTM]) AS DECIMAL(10,2)))
      ,SUM(CAST([Exception]) AS DECIMAL(10,2)))
      ,SUM(CAST([Training]) AS DECIMAL(10,2)))
      ,SUM(CAST([TOTAL Time]) AS DECIMAL(10,2)))
  FROM [a2hr].[dbo].[FinalPayroll] where period = 18

  SELECT
       [Convo]
      ,[F9D2]
      ,[F9D5]
      ,[F9DMTM]
      ,[Exception]
      ,[Training]
      ,[TOTAL Time]
  FROM [a2hr].[dbo].[FinalPayroll] where period = 18

November 8, 2021 at 8:08 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

ZZartin

SSC-Dedicated

Points: 30920

September 23, 2021 at 3:45 pm

#3932257

What is the exact problem?

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

Errors of 'Invalid Column Name': SUM(CAST([Convo] AS DECIMAL(10,2)))

Errors of 'Invalid Column Name': SUM(CAST([F902] AS DECIMAL(10,2))) SUM is not recognized as a valid function

etc.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

Does just this work?

SELECT
SUM(CAST([Convo] AS DECIMAL(10,2)))
FROM [a2hr].[dbo].[FinalPayroll] where period = 18

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

DaveBriCam wrote:

I'm needing to convert some varchar figures to decimal for sums, but can't quite find an example that works... the second block of code just returns the list of data without cast or sum:

Heh... lordy. At least do a desk check of your code. What is with all the extra parentheses right after the column names?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 5

Yes this by itself workss:

SELECT

SUM(CAST([Convo] AS DECIMAL(10,2)))

FROM [a2hr].[dbo].[FinalPayroll] where period = 18

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 6

Welp then start adding columns one by one until it breaks 🙂

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 7

OK my bad... got it... it was the extra parenthesis:

SELECT

SUM(CAST([Convo] AS DECIMAL(10,2)))

,SUM(CAST([F9D2] AS DECIMAL(10,2)))

,SUM(CAST([F9D5] AS DECIMAL(10,2)))

,SUM(CAST([F9DMTM] AS DECIMAL(10,2)))

,SUM(CAST([Exception] AS DECIMAL(10,2)))

,SUM(CAST([Training] AS DECIMAL(10,2)))

,SUM(CAST([TOTAL Time] AS DECIMAL(10,2)))