July 8, 2009 at 9:50 am
OK, I must be missing something obvious. Why does only the last calculation return a fractional value ??
select cast((7 /2)as decimal(9,2))-- 3.00
select cast((69 /60) as decimal(6,2))-- 1.00
select cast((138 /60) as decimal(4,2))-- 2.00
select cast((137418247 /10731824)as decimal(9,2))-- 12.00
select cast((13737418247 /1073741824) as decimal(9,2))-- 12.79
July 8, 2009 at 10:15 am
Not sure why the last is returning decimals, but the reason all the earlier ones are not is because you're dividing two ints. The result of that is also an int, which you're then casting to a decimal.
Cast one of the inputs to decimal and you'll see decimal places.
It could be that the last one the two values are two large to be ints so are considered to be float (maybe) instead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2009 at 10:15 am
Greetings,
The SQL engine will convert an integer number to a decimal number automatically when performing a math function and when one of the parts is a decimal number. In this case, your result is a decimal.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ee53c5c8-e36c-40f9-8cd1-d933791b98fa.htm
If you look about 1/3 down the page - under Remarks, it will talk about implicit conversions.
Two expressions can be combined by an operator if they both have data types supported by the operator and at least one of these conditions is true:
The expressions have the same data type.
The data type with the lower precedence can be implicitly converted to the data type with the higher data type precedence.
Have a good day.
Terry Steadman
July 8, 2009 at 1:01 pm
This is in books online:
SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.
So for your query to work you will have to do the following:
select cast((cast(13737418247 as bigint) /1073741824) as decimal(9,2))
July 8, 2009 at 1:02 pm
This is in books online:
SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.
So for your query to work you will have to do the following:
select cast((cast(13737418247 as bigint) /1073741824) as decimal(9,2))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply