SQL Server Float Datatype – How to Use with 2 Digits After Decimal Point

datatypessql serversql-server-2008-r2

In SQL server float datatype does not display trailing zeros after decimal point

declare @num as float
set @num=5.20
select @num

will dispaly only 5.2
But i want it to display as 5.20

After a little research i found that it can be solved by using

select CAST(@num AS numeric(10,2))

But this is not the solution i am expecting. Is there any method like setting datatype or changing datatype so that i can achieve the same?(It will be better if any variants are there in float which can do the same)

If it is possible then i need not change the SQL statement and include cast etc. So please help me on this.

Best Answer

This can be achieved with the decimal datatype.

See below for an example:

declare @num as float;
set @num=5.20;

select convert(decimal(10, 2), @num);

The output here will be 5.20.

After re-reading your question:

But this is not the solution i am expecting. Is there any method like setting datatype or changing datatype so that i can achieve the same?

Is there a reason why you are specifically using float? Oftentimes people tend to default to that datatype when decimal is more than sufficient.

Related Solutions

Sql-server – Column size with datatype decimal

Don't mix storage (5 or 9 bytes) and what you get back

In a decimal(19,5) column it is always 9 bytes on disk. So zero will take nine bytes.

Your 10999.99999 is merely a representation of that stored number that is then parsed as 10999.99999 by DATALENGTH

Edit:

DATALENGTH will return the number of bytes needed to store the expression given. It ignores datatype mostly. So 10999.99999 is treated as decimal(10,5) which can be stored in 5 bytes not 9 as Martin pointed out in a comment.

So a decimal(19,5) column is always 9 bytes. DATALENGTH doesn't care. It looks at the expression 10999.99999 and decides it can be stored in 5 bytes. But of course it can't

Personally, I've never used DATALENGTH except to find trailing spaces or such.

Basically, don't use DATALENGTH on non string datatypes.

Returns the number of bytes used to represent any expression.

DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

Note: LEN tells you how long the string representation is

DECLARE @i decimal(19,5)
SET @i ='10999.99999'
SELECT @i,LEN(@i),DATALENGTH(@i)
SET @i ='-90999.99999'
SELECT @i,LEN(@i),DATALENGTH(@i)

SSIS – Return Value Formatted as Money in SQL Server 2008 R2

This is really ugly and hopefully serves as a lesson as to why you should be fixing the receiving system to not be so fussy about trailing zeroes.

DECLARE @t TABLE(a DECIMAL(20,10));

INSERT @t VALUES
(1826.5695600000),  
(1826.3000000000),  
(  34.2300000000),  
(   0.0030000000);

DECLARE @d INT = 10; -- number of decimal places in source data!

;WITH s(a,dot,nz) AS 
(
  SELECT 
    CONVERT(VARCHAR(32),a),
    PATINDEX('%.%',CONVERT(VARCHAR(32),a)),
    PATINDEX('%[^0]%',REVERSE(a))
  FROM @t
)
SELECT LEFT(a,dot)+SUBSTRING(a,dot+1,@d+1-nz)
  + COALESCE(REPLICATE('0',nz-(@d-1)),'')
FROM s;

Results:

This assumes the source data type is a decimal(something,10). If it is a string, you need to have some extra precautions in here for values that aren't numbers (or aren't really numbers in this sense).

Best Answer

Related Solutions

Sql-server – Column size with datatype decimal

SSIS – Return Value Formatted as Money in SQL Server 2008 R2

Related Question