asked on

Error converting data type varchar to numeric.

this query
select PERIODO, SUM(CAST(DEBE AS decimal(10,2))) AS DEBE_MENSUAL,
SUM(CAST(HABER AS decimal(10,2))) AS HABER_MENSUAL
FROM [dbo].[Mayo]
GROUP BY PERIODO

has errror
Error converting data type varchar to numeric.

ASKER CERTIFIED SOLUTION

zephyr_hex (Megan)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

enrique_aeo

ASKER

this is results
PERIODO      DEBE      HABER
20150500             0.00
20150500             2124.00

enrique_aeo

ASKER

select PERIODO, SUM(CAST(DEBE AS decimal(10,2))) AS DEBE_MENSUAL,
SUM(CAST(HABER AS decimal(14,3))) AS HABER_MENSUAL
FROM [dbo].[Mayo]
--WHERE ISNUMERIC(DEBE)<>1 OR ISNUMERIC(HABER) <> 1
WHERE ISNUMERIC(DEBE)=1 OR ISNUMERIC(HABER) = 1
GROUP BY PERIODO

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.

enrique_aeo

ASKER

select PERIODO, SUM(CAST(DEBE AS decimal(10,2))) AS DEBE_MENSUAL,
SUM(CAST(HABER AS decimal(14,3))) AS HABER_MENSUAL
FROM [dbo].[Mayo]
--WHERE ISNUMERIC(DEBE)<>1 OR ISNUMERIC(HABER) <> 1
WHERE ISNUMERIC(DEBE)=1 AND ISNUMERIC(HABER) = 1
--AND DEBE IS NOT NULL
GROUP BY PERIODO

zephyr_hex (Megan)

Looks to me like you had two records where DEBE is '' (an empty space). If you can't update the records to either NULL or 0, you can test for the empty space and return 0 instead, before casting to decimal)

SUM(CAST((CASE WHEN DEBE = '' THEN 0 ELSE DEBE END) AS DECIMAL(10,2)))

Open in new window