SQL SUM of nvarchar to decimal

Hi everyone,

I have to work with some data that unfortunately is not very friendly in terms of allowing to use SUM. This is what I am trying to do ...

SELECT SUM(CAST(BidValue AS decimal)) AS BidValue FROM Portfolios

BidValue is nvarchar data type. There is nothing I can do to change this, but the issue I am having is I need it to return the decimal places, not round the returned value up or down.

Is there another data type I need to cast to, or is there something I need to add to this?

Thanks in advance.
DaiWilliamsAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
simply:

SELECT SUM(CAST(BidValue AS decimal(20,5) )) AS BidValue FROM Portfolios

Open in new window

0
 
Mohit VijayCommented:
SELECT SUM(CAST(BidValue AS decimal(15,7) )) AS BidValue FROM Portfolios
WHERe BidValue IS NOT NULL OR BidValue <> ''

7 is decimal places
0
 
Mohit VijayCommented:
A Correction..


SELECT SUM(CAST(BidValue AS decimal(15,7) )) AS BidValue FROM Portfolios
WHERe BidValue IS NOT NULL AND BidValue <> ''

7 is decimal places
0
 
Priya PerumpilavilSoftware EngineerCommented:
try this
SELECT  sum( convert (decimal,BidValue)) AS BidValue FROM Portfolios
0
All Courses

From novice to tech pro — start learning today.