Convert varchar to money datatype

[tavie]

Hi...I am trying to convert a varchar field to money but I am having no success. The problem is the decimal point. If I have a varchar field with data of 3599, I want it interpreted as $35.99. I have tried using convert but it failed everytime. I would appreciate any suggestions...Thanx

 

[cfk]

I'm going to assume that you store £12.50 as just that 1250.
In which case

SELECT 3599
---3599

SELECT CAST(3599 AS MONEY)
---3599.0000

SELECT CAST(CAST('3599' AS FLOAT)/100 AS VARCHAR(20))
---35.99

The reason for the second cast is MSSQL being useless with
floating point numbers.

SELECT CAST('3599' AS FLOAT)/100
---35.990000000000002

Although this is a tiny level of error.

 

[tlbroadbent]

Float and Real are approximate data type and perform exactly as described in the SQL Server documentation. If you want precise numeric representation use the Decimal or Numeric data types.

Select convert(decimal(8,2),convert(decimal, '3599')/100)

If your goal is to get a Precise number and you don't care about the display format the following will also work.

Select convert(money, '3599')/100

If you want to display the value with monetary symbol such as $ then you'll need to convert back to a char data type after doing the math.

Select
'$' + convert(varchar(12),
convert(money, '3599')/100, 0)
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm