Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Other SQL Server Topics / August 2005

Tip: Looking for answers? Try searching our database.

number format to 2 decimals

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
ypul - 09 Aug 2005 17:07 GMT
How can I format my data to 2 decimal numbers in sql ?

select numericColumn from table1

ex :-

numericColumn
---------------
10.22
20.00
30.45
12.02

regards
ypul
David Gugick - 09 Aug 2005 17:17 GMT
> How can I format my data to 2 decimal numbers in sql ?
>
[quoted text clipped - 11 lines]
> regards
> ypul

What data type are you using in the table?

Signature

David Gugick
Quest Software
www.imceda.com
www.quest.com

ypul - 09 Aug 2005 17:25 GMT
numericcolumn datatype is float

ypul

> > How can I format my data to 2 decimal numbers in sql ?
> >
[quoted text clipped - 13 lines]
>
> What data type are you using in the table?
David Gugick - 09 Aug 2005 23:13 GMT
> numericcolumn datatype is float
>
> ypul

Probably using the wrong data type. Why have you chosen to use an
approximate data type like float/real instead of DECIMAL/NUMERIC which
stores an exact represenation of a number? Do you really need to store.
From BOL:

"Approximate number data types for use with floating point numeric data.
Floating point data is approximate; not all values in the data type
range can be precisely represented."

If you either must use a float or cannot change the data type to
something more appropriate, you have two options:
1- Format the numeric data on the client - client formatting is
preferred over using SQL Server to do the same
2- Use the CAST function, but you may have to deal with rounding issues

For example:

Declare @f real
Set @f = .05
Select @f -- Returns 5.5500001E-2
Select CAST(@f as NUMERIC(10, 2)) -- Returns .06

Signature

David Gugick
Quest Software
www.imceda.com
www.quest.com

ypul - 10 Aug 2005 09:28 GMT
Thanks Buddy
that was very very ......helpful !!

ypul

> > numericcolumn datatype is float
> >
[quoted text clipped - 21 lines]
> Select @f -- Returns 5.5500001E-2
> Select CAST(@f as NUMERIC(10, 2)) -- Returns .06
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.