how to change the format of the numeric values to be numeric with 2 decimal places

Question

question

sam09 asked Jan 5, '16

how to change the format of the numeric values to be numeric with 2 decimal places

how to change the format of the numeric values to be numeric with 2 decimal places. The current layout shows them in text with leading zero’s code in procedure is SUBSTRING(data,232,11)as [REFERED_AMT], SUBSTRING(data,232,11)as [PRINCIPLE_AMT], 13642 ,i want it to be 136.42 ![alt text][1] [1]: /storage/temp/3148-1.png

proc

1.png (4.3 KiB)

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sam09 commented · Jan 07, 2016 at 10:18 AM

cast((cast(SUBSTRING(data,232,11) as float)/100.0) as numeric(18,2)) as [REFERED_AMT], im getting this error while editing sp Error converting data type varchar to numeric.

0 ·

Grant Fritchey ♦♦ commented · Jan 10, 2016 at 05:32 PM

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question. You can mark your own answer as the solution.

0 ·

sam09 commented · Jan 06, 2016 at 09:16 AM

i have CONVERT(varchar,SUBSTRING(data,232,11) /1) as [REFERED_AMT], 13642 And i want it to be 136.42

-1 ·

Gazz sam09 commented · Jan 06, 2016 at 09:24 AM

then try doing what I said instead.... CONVERT(MONEY, SUBSTRING(data,232,11)) / 100 as [REFERED_AMT]

0 ·

Gazz sam09 commented · Jan 06, 2016 at 09:43 AM

It also seems you could do this: SUBSTRING(data,1,2)/1+.0)/100 Not sure exactly why it works, but it seems dividing by 1 changes it to a numeric output then adding .0 changes it to a decimal.

0 ·

Answer 1 · 2016-01-05T14:24:37Z

Gazz answered Jan 5, '16

CONVERT(INT, SUBSTRING(data,232,11)) / 100 as [REFERED_AMT] [ http://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/][1] [1]: http://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM commented · Jan 05, 2016 at 02:32 PM

INT won't work as you can't have decimal points with an INT. The MONEY data type though would work out great if it's currency that's being represented here. Good solution. Better than the one that I came up with.

1 ·

Gazz commented · Jan 05, 2016 at 02:43 PM

oh yeh, silly me. At least what you posted would have actually worked!

1 ·

Answer 2 · 2016-01-05T14:24:49Z

JohnM answered Jan 5, '16

If I understand correctly, would something like this work? You could wrap this around the substring to represent @amt. DECLARE @amt VARCHAR(100) = '12311510000001364212123123' SELECT LEFT(CAST(SUBSTRING(@amt,8,11) AS NUMERIC),LEN(CAST(SUBSTRING(@amt,8,11) AS NUMERIC)-2))+'.'+RIGHT(SUBSTRING(@amt,8,11),2) You can then cast/convert the final value to whatever numeric data type you want. Hope that helps!!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2016-01-07T10:53:28Z

sam09 answered Jan 7, '16

Thanx every1 i have solved it CAST(cast((cast(SUBSTRING(data,232,11) as float)/100.0) as numeric(18,2)) as varchar) as [REFERED_AMT],

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2016-01-12T09:24:03Z

aas answered Jan 12, '16

select format((13264/100.0),'n2') as [REFERED_AMT]

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question