CODEWITHSUNDEEP
sqlsql-server
AndroidAL
AndroidAL

Reputation: 1119

Convert gives an error

When I run this query I get Incorrect syntax error "(". Is this the correct way of using CONVERT?

SELECT
    DATEPART(mm,[actualclosedate]) AS [Month],
    COUNT([opportunityid]) AS 'Won Opportunities',
    CONVERT(SUM(ISNULL([actualvalue],1)) as numeric (10,2)) AS 'Value'

FROM  [dbo].[FilteredOpportunity]
WHERE [owneridname]= @SalesPerson   
    AND YEAR([actualclosedate]) = @Year

GROUP BY 
    DATEPART(mm,[actualclosedate])`

Upvotes: 1

Views: 62

Answers (3)

Ed Elliott
Ed Elliott

Reputation: 6856

If you use convert you put the type first before the expression:

SELECT
DATEPART(mm,[actualclosedate]) AS [Month],
COUNT([opportunityid]) AS 'Won Opportunities', 
CONVERT(numeric (10,2),
        SUM(
                ISNULL([actualvalue],1)
            )
        ) AS 'Value'

 FROM  [dbo].[FilteredOpportunity]
 WHERE [owneridname]= @SalesPerson   
 AND YEAR([actualclosedate]) = @Year

 GROUP BY 
    DATEPART(mm,[actualclosedate])

Upvotes: 2

Galma88
Galma88

Reputation: 2546

This should be the correct statement:

SELECT
    DATEPART(mm,[actualclosedate]) AS [Month],
    COUNT([opportunityid]) AS 'Won Opportunities',
    CAST(SUM(ISNULL([actualvalue],1)) as numeric (10,2)) AS 'Value'

FROM  [dbo].[FilteredOpportunity]
WHERE [owneridname]= @SalesPerson   
    AND YEAR([actualclosedate]) = @Year

GROUP BY 
    DATEPART(mm,[actualclosedate])`

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Instead of CONVERT() use CAST() function.

Syntax

CAST ( expression AS data_type )

Upvotes: 3

Related Questions