Testing whether TRY_CONVERT is losing precision

Question

Testing whether TRY_CONVERT is losing precision

Chrissy321

SSCoach

Points: 15776
More actions
December 26, 2024 at 4:35 pm

#4505275
Hello All,
I am using TRY_CONVERT to parse character data into a decimal column. I want to generate alerts when the value is not null and TRY_CONVERT returns a null. That works.
SELECT '5254.15',TRY_CONVERT(DECIMAL(5,2),'5254.15')
But I am running into situations where TRY_CONVERT truncates precision. In the cases I want to be alerted.
SELECT '6.125',TRY_CONVERT(DECIMAL(5,2),'6.125')
Any suggestions on how I can refine my test to catch these situations? Thanks all
```
DROP TABLE IF EXISTS #Test 
CREATE TABLE #Test (TestValue VARCHAR(20))
INSERT INTO #Test SELECT '2.55'--OK  
INSERT INTO #Test SELECT '6.125'--Should be Not OK since I am losing precision
INSERT INTO #Test SELECT '7.1500000'--OK since I am not using precision
INSERT INTO #Test SELECT '5254.15'--Not OK-This is the desired result

SELECT 
TestValue,
TRY_CONVERT(DECIMAL(5,2),TestValue),
CASE WHEN TestValue IS NOT NULL AND TRY_CONVERT(DECIMAL(5,2),TestValue) IS NULL THEN 'Not OK' ELSE 'OK' END
FROM #Test
```

Viewing 3 posts - 1 through 2 (of 2 total)

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

ScottPletcher SSC Guru Points: 101178 More actions · Answer 1

I think you'll have to check for that yourself, maybe something like this:

SELECT CASE WHEN TRY_CONVERT(DECIMAL(9,6),'6.125') <> TRY_CONVERT(DECIMAL(5,2),'6.125') THEN 'Loss of precision error' ELSE 'Good' END

SELECT CASE WHEN TRY_CONVERT(DECIMAL(9,6),'7.1500000') <> TRY_CONVERT(DECIMAL(5,2),'7.1500000') THEN 'Loss of precision error' ELSE 'Good' END

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Chrissy321 SSCoach Points: 15776 More actions · Answer 2

Thanks Scott. I suspected this would be CIY-code it yourself. The other approach I considered is not inserting the bad row at all. SSIS has error output which does this. BULK INSERT has the ERRORFILE argument. TSQL does not have anything similar as far as I know and that makes sense since that would be inconsistent with a set based approach.

Testing whether TRY_CONVERT is losing precision

Cookies on SQLServerCentral