CODEWITHSUNDEEP
sqlcastingnumber-formatting
Rick Coleman
Rick Coleman

Reputation: 454

Formatting Decimal with SQL

How can I format a number like 0731 to 07.31 and keeping the leading 0. I was running this query, it works but it removes the leading 0 that I want to keep.

CAST(CAST(proc_P AS decimal(4,0))/100 AS Decimal(4,2))

Upvotes: 0

Views: 105

Answers (2)

Yuriy Galanter
Yuriy Galanter

Reputation: 39807

Assuming that proc_P is a varchar (having leading 0), try this:

SELECT LEFT(proc_P, LEN(proc_P) - 2) + '.' + RIGHT(proc_P,2)

Upvotes: 0

Sparky
Sparky

Reputation: 15115

Try this:

select proc_P,RIGHT('000'+CAST(CAST((CAST(proc_P as decimal(4,0))/100) as decimal(4,2)) as VARCHAR(5)),5)
from test

SQLFiddle: http://www.sqlfiddle.com/#!3/69b09/13

Upvotes: 1

Related Questions