views:

372

answers:

2

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
+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
.0 is required because you casted to int; casting to decimal don't requires it
Rubens Farias
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
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