Always Show Decimal Places in SQL

How to display two digits after decimal point in SQL Server

select cast(your_float_column as decimal(10,2))
from your_table

decimal(10,2) means you can have a decimal number with a maximal total precision of 10 digits. 2 of them after the decimal point and 8 before.

The biggest possible number would be 99999999.99

Always show decimal places in SQL?

TO_CHAR(pAmount, '9,999,999.99');

http://www.techonthenet.com/oracle/functions/to_char.php

http://www.ss64.com/orasyntax/to_char.html

How to always show two decimal places on a number in PL/SQL

You actually, have to insert the data with formatting. (Assuming the targeted column is VARCHAR) What ever format you fetch and put into a NUMBER variable, will be a NUMBER.

A number doesn't have a format to be saved after-all. Only for display, the formatting comes into picture.

insert into SQL_TXT values (TO_CHAR(v_credit_amt,'FM99999.00'));
commit;

If the INSERT-ed column is NUMBER again.

You still want to go with

SELECT TO_CHAR(COL1,'FM99999.00') FROM SQL_TEXT;

Format number to show at least 1 decimal

This seems like an odd requirements, but you can use to_char() with a suitable format model; just using `999.099999999' wouldn't quite work though:

with t (n) as (
  select 100 from dual
  union all select 100.0 from dual
  union all select 100.6 from dual
  union all select 100.654 from dual
  union all select 100.65468151 from dual
)
select n, to_char(n, '999999999.09999999') as text
from t;

         N TEXT               
---------- -------------------
       100        100.00000000
       100        100.00000000
     100.6        100.60000000
   100.654        100.65400000
100.654682        100.65468151

But if you add the FM format modifier then extra trailing zeros won't be included:

with t (n) as (
  select 100 from dual
  union all select 100.0 from dual
  union all select 100.6 from dual
  union all select 100.654 from dual
  union all select 100.65468151 from dual
)
select n, to_char(n, 'FM999999999.09999999') as text
from t;

         N TEXT               
---------- -------------------
       100 100.0              
       100 100.0              
     100.6 100.6              
   100.654 100.654            
100.654682 100.65468151       

You need an appropriate number of 9s before and after the decimal point so all possible values can be rendered of course; and you may prefer to make the final 9 before the decimal point a zero. You might also want to consider using the D format element instead of a period . so it honours session NLS settings.

Or, let your presentation layer (application, reporting tool or whatever) do the formatting. You should leave it as an actual number until the last possible moment.

Rounding off to two decimal places in SQL

You could cast your result as numeric(x,2). Where x <= 38.

select
    round(630/60.0,2),
    cast(round(630/60.0,2) as numeric(36,2))

Returns

10.500000    10.50

Format to significant decimal places in SQL Server?

I would strongly suggest to perform formatting-related tasks on the client side, for a multitude of reasons. However, starting from SQL Server 2012, you can do this:

select MyField, format(MyField, '0.##') as [MyFieldFormatted]
from dbo.MyTable;

SQL , format all numbers to 2 decimal places (eg 20 to 20.00)

In general, such conversions are both database-specific and GUI-specific. However, the database can convert the value to something with two decimal places by using numeric/decimal (those are equivalent):

select cast(value as numeric(10, 2))

The "2" is the two digits after the decimal place. This should be displayed with two digits -- in any reasonable interface.

If you are using MySQL (as PHP suggests), you can use the format() function to accomplish this:

select format(value, 2)

---

Related Topics