Error converting data type varchar to numeric in table with different data types

Question

I want to select data from a column with between operator Looks like this:

select count(distinct value) 
  from dbo.OrganizationSupplementalData
 where value between 5 and 30;

but getting

Error converting data type varchar to numeric

I also tried using CAST but it doesn't work:

select count(distinct value) 
  from dbo.OrganizationSupplementalData
 where cast(value as decimal(22, 8)) between 0.1 and 30.00;

In value column resides these types of data:

2017
2017
2017
2017
8:55:00 AM
11:00:00 AM
8:00:00 AM
8:45:00 AM
3.66
2.35

How can I eventually extract needed data? Thanks!

Why are you storing time, integer and decimal values in a single column (which is none of those types). The real problem here is your data and design. — Thom A
– Thom A ♦, Commented Jul 14, 2019 at 13:47

Gordon Linoff · Accepted Answer · 2019-07-14 13:27:36Z

2

Use TRY_CAST():

where TRY_CAST(value AS DECIMAL(22, 8)) between 0.1 and 30.00

Obvious, the non-number values are causing a problem when you try to convert them to numbers. TRY_CAST() will return NULL in this case.

In older, unsupported versions of SQL Server, you can use a case expression:

where (case when value not like '%[0-9.]%' and
                 value not like '%.%.%'
            then CAST(value AS DECIMAL(22, 8))
       end)

answered Jul 14, 2019 at 13:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Barbaros Özhan Over a year ago

Wow very nice function, even version '14 supports.

Alexander Tunick Over a year ago

Gordon Linoff, God bless you, man! Thank you very much. I couldn't understand why it doesn't convert it. Now understand the mistake and how it can be handled in the future. Thank you indeed.

Dan Guzman Over a year ago

@AlexanderTunick, adding that having different data types in the same column may indicate a flawed data model. Addressing the root cause may avoid the need for TRY_CAST or other workarounds.

Alexander Tunick Over a year ago

@DanGuzman exactly! Cannot but agree with you! But one not, we work with client's database and it's complete landfill. Recommendations that we give to them are get taken in progress and they fix it but this process quite slow.

Collectives™ on Stack Overflow

Error converting data type varchar to numeric in table with different data types

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related