return of decimal places in sqlserver queries

Question

return of decimal places in sqlserver queries

Navigation

#1 by (4 votes)
#2 by (0 votes)

1

In the query below I would like to return only 3 boxes after the comma for the columns: size in mb, availablespaceinmb As for column available, I would like to return with no decimal places after the comma.

How can I do this.

SELECT
rtrim(name)as name,
((size)/128.0) as'size in MB',
((size)/128.0) -CAST(FILEPROPERTY(name,'SpaceUsed')AS int)/128.0 AS AvailableSpaceInMB,
((((size)/128.0) -CAST(FILEPROPERTY(name,'SpaceUsed')AS int)/128.0)/((size)/128.0))
* 100 as'% Available', filename
FROM sysfiles

sql-server

asked by anonymous 28.10.2014 / 20:32

2 answers

0

Hello, to bring the results with with decimal separator I did so.

DECLARE @VALOR  DECIMAL(15,2)
DECLARE @X  INT

SET @VALOR  = 2111113.55
SET @X = CASE   WHEN LEN(@VALOR) BETWEEN 1 AND 6 THEN 3
                WHEN LEN(@VALOR) BETWEEN 7 AND 9 THEN 2
                WHEN LEN(@VALOR) BETWEEN 10 AND 12 THEN 1 
                WHEN LEN(@VALOR) BETWEEN 13 AND 15 THEN 0 END

SELECT REPLACE(LEFT(convert(varchar,cast(@VALOR as money),1),(LEN(@VALOR)-@X)),',','.')  + ',' + RIGHT(@VALOR,2)

25.07.2016 / 19:27

How to manipulate Iterator position Check a character pattern for login

score 4 · Accepted Answer

If you are talking about presenting the result and using MS SQL Server 2012 or higher, you can use the FORMAT function to determine the number of houses after the comma, like this:

select format(1234.5678, '#.000')

The result will be 1234.568 (see that it has been rounded).

And, to display without any decimal places:

select format(1234.5678, '#')

The result will be 1235 (also rounded).

Update: In earlier versions, you can use the ROUND :

select round(1234.5678, 3)
select round(1234.5678, 0)

Results: 1234.568 and 1235 .

Or you can convert the value to DECIMAL by limiting the number of decimal places:

select cast(1234.5678 as decimal(10, 3))
select cast(1234.5678 as decimal(10, 0))

Results: also 1234.568 and 1235 .

At your command, simply apply the chosen method on each column resulting from the SELECT command. For example:

SELECT
    rtrim(name)as name,
    ROUND(((size)/128.0), 3) as'size in MB',
    ROUND(((size)/128.0) -CAST(FILEPROPERTY(name,'SpaceUsed')AS int)/128.0, 3) 
            AS AvailableSpaceInMB,
    ROUND(((((size)/128.0) 
            -CAST(FILEPROPERTY(name,'SpaceUsed') AS int)/128.0)
            /((size)/128.0)) * 100, 0) as'% Available', 
    filename
FROM 
    sysfiles