T sql cast string value as money format not working

Question

I have values as string format below,

Values :

-24,52

-22,74

-13,08

303,75

When i convert above string values as money all values displays as below

All values have more "0" number problem.How can i convert string values to exact money format ?

its not "money format" its money data type (basically: decimal). Formatting is a job for your UI, not your RDBMS — Jamiec, Sep 22 '15 at 09:51
You should wash your data *before* casting, to avoid handling legitimate data afterwards. For the future (or even now if you can change it), if you need to store numbers in the database, don't pick a text data type. — Lasse V. Karlsen, Sep 22 '15 at 09:55

score 1 · Accepted Answer · edited Sep 22 '15 at 10:01

1

You can try to remove the 00 like this:

select replace(convert(varchar,cast(yourNum as money),1), ',00','')

Or in SQL Server 2012 you can try to use FORMAT

SELECT Format(24.01, '##,##0')

edited Sep 22 '15 at 10:01

Lukasz Szozda

162,964
23
234
275

answered Sep 22 '15 at 09:52

Rahul Tripathi

168,305
31
280
331

Replace works on string, so there is one implicit conversion to varchar – Lukasz Szozda Sep 22 '15 at 09:53
@lad2025:- Updated that! – Rahul Tripathi Sep 22 '15 at 09:56
`SELECT Format(24.01, '##,##0') ` :), `24,01` is treated as 2 arguments, use `.` for decimal point – Lukasz Szozda Sep 22 '15 at 09:58
i tried your replace method @RahulTripathi as instance: string value is -24,52 when i used your method it displays -2,452.00 .Value should be -24,5200 how can i do this like this ? – John Richard Sep 22 '15 at 10:03

score 1 · Answer 2 · edited May 23 '17 at 11:58

1

select cast('-24,01' as money) / 100

For anything other than visual presentation its a good idea to avoid MONEY entirely.

edited May 23 '17 at 11:58

Community

1
1

answered Sep 22 '15 at 09:54

Alex K.

171,639
30
264
288

score 1 · Answer 3 · answered Sep 22 '15 at 09:56

1

try this query

WITH t
AS
(
    SELECT -24.52 AS [val]
    UNION
    SELECT -22.74
    UNION
    SELECT -13.08
    UNION
    SELECT 303.75
)

SELECT CAST( ABS(t.val) AS decimal(10,2)) -- or decimal(10,0)
FROM t

answered Sep 22 '15 at 09:56

Chanom First

1,136
1
11
25

A ツ · Answer 4 · 2015-09-28T14:50:49.893

1

i tried to replace the comma with a point and got lucky:

cast(replace('-12,23', ',', '.') as money)

the reason is, sql server treats ',' as thousend delimiter instead of decimal delimiter

edited Sep 28 '15 at 14:50

answered Sep 22 '15 at 14:01

A ツ

1,267
2
9
14

T sql cast string value as money format not working

4 Answers4