I have this data as varchar '00072330'. How do I convert it to a decimal that looks like '723.30' in SQL Server 2008?
+3
A:
Try this:
declare @data as varchar(8)
set @data = '00072330'
print cast(@data as decimal) / 100
Rubens Farias
2009-10-19 16:34:24
+2
A:
This:
SELECT CAST('00072330' AS INT)/100.0
...will give you:
723.300000
The .0 is important, otherwise SQL Server will perform integer math.
OMG Ponies
2009-10-19 16:38:52
.0 is required because you casted to int; casting to decimal don't requires it
Rubens Farias
2009-10-19 16:40:27
Yes, but why not be more explicit when you cast as decimal? If you know you are limited to 8 characters (two decimal places) I would much rather see AS DECIMAL(8,2) than just AS DECIMAL. YMMV.
Aaron Bertrand
2009-10-20 01:58:00
I think the point of the conversion is because the precision is not consistent. But you make a good point about setting the precision on the cast/convert. I'm still not up to speed on my SQL Server datatypes.
OMG Ponies
2009-10-20 02:22:38