3 replies

Last post Jun 13, 2015 04:14 PM by fungus.00

• fungus.00

Member

62 Points

181 Posts

How to get two digits after decimal SQL Server 2008

Hi all,

Calculating file size

```DECLARE @fileSize  DECIMAL(18, 2) = 2073741824

SELECT CASE
WHEN @fileSize < 1024
THEN CONVERT(NVARCHAR, @fileSize) + ' B'

WHEN @fileSize >= 1024 AND @fileSize <= 1048575
THEN CONVERT(NVARCHAR, ROUND(@fileSize / 1024, 2)) + ' KB'

WHEN @fileSize >= 1048576 AND @fileSize <= 1073741823
THEN CONVERT(NVARCHAR, ROUND(@fileSize / 1048576, 2)) + ' MB'
ELSE CONVERT(NVARCHAR, ROUND(@fileSize / 1073741824, 2)) + ' GB'
END```

it returns

(No column name)
1.9300000000000 GB

I want to keep 2 digits after decimal like this

1.93 GB

How to get 2 digits after decimal ?

sds

• Rajneesh Ver...

All-Star

28988 Points

7250 Posts

Re: How to get two digits after decimal SQL Server 2008

Jun 13, 2015 01:52 AM|Rajneesh Verma|LINK

Do as below:

```DECLARE @fileSize  DECIMAL(18, 2) = 2073741824

SELECT CASE
WHEN @fileSize < 1024
THEN CONVERT(NVARCHAR, @fileSize) + ' B'
WHEN @fileSize >= 1024
AND @fileSize <= 1048575
THEN CONVERT(NVARCHAR, CAST(ROUND(@fileSize / 1024, 2) AS NUMERIC(36, 2))) + ' KB'
WHEN @fileSize >= 1048576
AND @fileSize <= 1073741823
THEN CONVERT(NVARCHAR, CAST(ROUND(@fileSize / 1048576, 2) AS NUMERIC(36, 2))) + ' MB'
ELSE CONVERT(NVARCHAR, CAST(ROUND(@fileSize / 1073741824, 2) AS NUMERIC(36, 2))) + ' GB'
END
```

• vahid bakkhi

Star

8059 Points

2780 Posts

Re: How to get two digits after decimal SQL Server 2008

Jun 13, 2015 01:54 AM|vahid bakkhi|LINK

hi

you must cast it to float  below like :

```DECLARE @fileSize  DECIMAL(18, 2) = 2073741824

SELECT CASE
WHEN @fileSize < 1024
THEN CONVERT(NVARCHAR, @fileSize) + ' B'

WHEN @fileSize >= 1024 AND @fileSize <= 1048575
THEN CONVERT(NVARCHAR,cast( ROUND(@fileSize / 1024, 2))as float) + ' KB'

WHEN @fileSize >= 1048576 AND @fileSize <= 1073741823
THEN CONVERT(NVARCHAR,cast( ROUND(@fileSize / 1048576, 2)) as float) + ' MB'
ELSE CONVERT(NVARCHAR, cast( ROUND(@fileSize / 1073741824, 2)as float)) + ' GB'
END```

vahid bakhtiary