CODEWITHSUNDEEP
sqlsql-serverdata-analysisdata-manipulation
user7298979
user7298979

Reputation: 549

Splitting field and adding decimal point to create numeric value SQL

I have a field in SQL Server 2014 that I am working with that looks like this:

**RawField**
20060202
20060323

I want to add a split the field and add a decimal point and create a numerical field. This is what I would like to see:

**RawField**
200602.02
200603.23

So I need to split the field, add the decimal point, and convert to a numerical value. I tried some code but was getting an error. Please see my code below:

  select top 1000 cast(SUBSTRING(cast(RawField as varchar(6)),1,6) + cast('.' as varchar(1)) + SUBSTRING(cast(RawField as varchar(2)),6,2) as int)
  from Table

I get an error of:

Msg 245, Level 16, State 1, Line 11
Conversion failed when converting the varchar value '200602.' to data type int.

Is this a good approach?

Upvotes: 0

Views: 709

Answers (3)

Fahmi
Fahmi

Reputation: 37473

Use float/numeric/decimal when casting

select cast(SUBSTRING(RawField ,1,6) + cast('.' as varchar(1)) + SUBSTRING(RawField ,7,2) as numeric(16,2))

Upvotes: 0

Squirrel
Squirrel

Reputation: 24773

you want to convert the string to numeric with 2 decimal places ?

select convert(decimal(10,2), RawField) / 100.0

I guest your RawField contains other alphanumeric after that and you only posted the first 8 characters ?

this should work. Just take the first 8 characters and convert. Simple and direct

select convert(decimal(10,2), left(RawField, 8)) / 100.0

Upvotes: 2

nitzien
nitzien

Reputation: 1267

Please try this.

select top 1000 cast(SUBSTRING(cast(RawField as varchar(6)),1,6) + cast('.' as varchar(1)) + SUBSTRING(cast(RawField as varchar(2)),6,2) as numeric(8,2))
  from Table

You are trying to cast string with decimal number to int.

Upvotes: 1

Related Questions