needing help casting varchar to decimal

  • 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

     

     

  • What is the exact problem?

  • 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.

  • Does just this work?

     

    SELECT
    SUM(CAST([Convo] AS DECIMAL(10,2)))
    FROM [a2hr].[dbo].[FinalPayroll] where period = 18
  • 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)
    Intro to Tally Tables and Functions

  • Yes this by itself workss:

    SELECT

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

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

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

     

  • 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)))

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

  • ZZartin wrote:

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

    Good lesson on basic troubleshooting.  Sorry I almost screwed that all up with my post about desk checking.

    --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)
    Intro to Tally Tables and Functions

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

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