Format decimal to have zero in front

Question

Format decimal to have zero in front

ramadesai108

SSChampion

Points: 10609
More actions
January 12, 2012 at 9:36 am

#249068

Hi All,
I have a view where I get the result from a decimal as 8.55, 90.07, etc. I want the result as 08.55 and 90.07. I tried casting it as varchar adding '0' at the front. But that gives me 909.07 and 08.55. How can I achieve this?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

ramadesai108 (1/12/2012)
Hi All,
I have a view where I get the result from a decimal as 8.55, 90.07, etc. I want the result as 08.55 and 90.07. I tried casting it as varchar adding '0' at the front. But that gives me 909.07 and 08.55. How can I achieve this?
Thanks.

DDL of your view please

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Cadavre (1/12/2012)
ramadesai108 (1/12/2012)
Hi All,
I have a view where I get the result from a decimal as 8.55, 90.07, etc. I want the result as 08.55 and 90.07. I tried casting it as varchar adding '0' at the front. But that gives me 909.07 and 08.55. How can I achieve this?
Thanks.
DDL of your view please

Yes please. The 909.07 is a very interesting result that the DDL may explain.

Test data would be very helpful too.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramadesai108 SSChampion Points: 10609 More actions · Answer 3

I should have searched on this site before posting, I apologize. I found the solution on this site as:

REPLICATE('0',(3-len(floor(Entry))))+ cast(Entry as nchar(20))

Thanks to whoever contributed the solution.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Cool - glad you found the answer.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

An alternative method...

WITH SampleData (Entry) AS (

SELECT 8.55

UNION ALL

SELECT 90.07

)

SELECT Solution = REPLICATE('0',(3-len(floor(Entry))))+ cast(Entry as nchar(20)),

Alternative = REPLACE(STR(Entry,6,2),SPACE(1),'0')

FROM SampleData

-- Gianluca Sartori

ramadesai108 SSChampion Points: 10609 More actions · Answer 6

Sartori,

I liked your alternate solution better. It converts to string, but how do I convert the result back to decimal?

Thanks.

ramadesai108 SSChampion Points: 10609 More actions · Answer 7

I could not convert it to string because some of the data is not in decimal format. So my issue is now resolved.

Thanks for your help.

Johan Bijnens SSC Guru Points: 135262 More actions · Answer 8

with numeric data, leading zeroes do not play a significant role, so don't worry.

If you want leading zeroes in your result, handle that in your presentation layer (gui, report, ...)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me