Query returning Int instead of Decimal Value ?

[Luqman]

Hi,

I am using Sql Server 2008,

Why the following Query returning the Answer : 3
When it should return : 32.692

SELECT CAST(32692/1000 AS decimal(12,5))

Kindly adv.

Thanks and Regards,

Luqman

[Tom Cooper]

You are dividing two numbers of type int (32692 and 1000) so the result is
an int, namely 32. Then you case that as a decimal with 5 decimal places
and you get 32.00000. To fix, make at least one of your numbers a decimal
before you divide, either by putting a decimal point on one of them or
casting one of them as decimal.

SELECT CAST(32692./1000 AS decimal(12,5))
or
SELECT CAST(CAST(32692. AS decimal(12,5))/1000 AS decimal(12,5))

Tom

"Luqman" <pear...@cyber.net.pk> wrote in message
news:76B6B2D6-C37A-46A3...@microsoft.com...

[Luqman]

Thanks Tom, very strange, even Oracle does not behave like this.

In oracle, Select 32692/1000 from dual returns : 32.692

No conversion needed.

Well, thanks for the help.

"Tom Cooper" <tomc...@comcast.net> wrote in message
news:%237iA%23G1QL...@TK2MSFTNGP04.phx.gbl...

[Erland Sommarskog]

Luqman (pear...@cyber.net.pk) writes:
> Thanks Tom, very strange, even Oracle does not behave like this.

There are several other languages that do. I know that Fortran does.
There may be other languages as well.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx