Number Format

Question

Number Format

JulieW

Say Hey Kid

Points: 680
More actions
November 19, 2003 at 5:00 am

#358686

Hello,
I was wondering if anyone could help me with an SQL Statement that will always return a number formatted with two decimal places.
Thanks
J

Viewing 13 posts - 1 through 12 (of 12 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

something like this



declare  @test int

set  @test=1



select cast(@test as decimal(8,2))

           

---------- 

1.00



(1 row(s) affected)

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Crispin Proctor SSCoach Points: 18161 More actions · Answer 2

Select Convert(Decimal(10, 2), 12.23653)

You can replace the number with the coulmn name.

Cheers,

Crispin

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

JulieW Say Hey Kid Points: 680 More actions · Answer 3

JulieW

Say Hey Kid

Points: 680

November 19, 2003 at 5:11 am

#482590

Thanks Chaps

J

JulieW Say Hey Kid Points: 680 More actions · Answer 4

Hi,

Sorry but that did not work.

We have some amounts without decimal places, and those have not been given them. I have tried both of the guys examples, and both fail. Help please ??

J

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Can you post what you have done?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

JulieW Say Hey Kid Points: 680 More actions · Answer 6

This is really odd.

I was doing the SQL in the view creator, and putting in the code you guys supplied resulted in the amounts without any decimal places, not displaying in the view.

However I then put the same code in Query Analyser, and the results had the decimal places.

Is this a bug with the view creater in Enterprise Manager ?

Thanks

J

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Another reason not to use EM unless absolutely necessary.



CREATE VIEW dbo.VIEW2

AS

SELECT     CAST(OrderzettelID AS decimal(8, 2)) AS a

FROM         dbo.tblOrderzettel

GO

I've created the view in EM with the output you've mentioned.

However QA (and I guess any other front-end) will return the expected result.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

Edited by - Frank kalis on 11/19/2003 05:37:10 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

JulieW Say Hey Kid Points: 680 More actions · Answer 8

Does anyone who finds a SQL Server Bug get a prize lol, seriously though how do you report this to Microsoft ?

J

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

I guess somebody would get very rich

Seriously, I don't know how to report this. Microsoft Produkt Service. Hotline?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 10

Microsoft will not say this is a bug, this is just a feature.

Gregory A. Larsen, DBA

Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp

Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 11

Opps forgot to add the link. You might want to try to use one of the links on this page to report the feature you found.

http://support.microsoft.com/default.aspx?scid=fh;en-us;feedback

Gregory A. Larsen, DBA

Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp

Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

JulieW Say Hey Kid Points: 680 More actions · Answer 12

JulieW

Say Hey Kid

Points: 680

November 20, 2003 at 6:55 am

#482844

Thank you

J