Splitting field and adding decimal point to create numeric value SQL
Clash Royale CLAN TAG#URR8PPP
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?
DATE
Why are you trying to cast
200602.02 as an INT?– Tab Alleman
1 min ago
200602.02
@JeroenMostert Originally it's a varchar(50). It was a file dump from a text file
– user7298979
18 secs ago
1 Answer
1
you want to convert the string to numeric with 2 decimal places ?
select convert(decimal(10,2), RawField) / 100.0
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
What's the original type of the column, sans all the casting? (Also, is this possibly a
DATEin poor disguise, instead of anything numeric?)– Jeroen Mostert
1 min ago