Generally for storing a variable like 123.45 we can use a decimal or numeric data type. The usual notation for these data types are decimal [ (p[ ,s] )] & numeric[ (p[ ,s] )], p stands for precision & s stands for scale.
Precision is number digits in variable
Scale is of digits to the right of the decimal point
For ex: 12.345 p = 5 & s = 3
The sample query explains the behavior of the decimal, float & int
declare @t table(
c1 int,
c2 float,
c3 decimal(18,2)
)
insert into @t (c1,c2,c3) select 12.345, 12.345, 12.345
insert into @t (c1,c2,c3) select 10/3, 10/3, 10/3
insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, 10/3.0
insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, round(10/3.0, 2)
select * from @t
c1 c2 c3
12 12.345 12.35
Note: The value in C3 is truncated to two decimal places, C1 completely ignored the fraction part.
3 3 3.00
Note: The fraction part is completely ignored because the denominator is an integer
3 3.333333 3.33
Note: The fraction part is retained correctly
3 3.333333 3.33
Note: The fraction part can also be achieved by rounding the value using round function
No comments:
Post a Comment