Select does not return broken value

Asked

Viewed 146 times

1

I have the following command:

 SELECT CAST(450/4 AS DECIMAL(15,2))

Return: 911 where the right one would be 112.5

Because of that?

NOTE: My SCRIPT is in a precedent, but roughly my problem is in this select

sql sql-server sql-server-2012
  • 2

    According to the documentation (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/divide-transact-sql), the type of return of the division will be the highest kind of precedence, between divisor and dividend, and when the two are integers the result is also an integer and will be truncated, Try it like this, then: 450.0/4.0, that probably the result will be of the type decimal.

  • This is precisely the answer @Pedrogaspar-Lobofx. Post it as such

  • Exactly worked that way.

1 answer

1


According to the documentation:

/ (Division) (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/divide-transact-sql

the division return type will be the highest precedence type (Data type precedence), between divisor and dividend, and when the two are integer, the result is also an integer and will be truncated.

Do it this way, then, that the result of the division will be of the type decimal:

SELECT CAST(450.0/4.0 AS DECIMAL(15,2))

When writing a decimal point numeric constant, SQL Server will understand that that number is of the type decimal:

Constants (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql

Like the guy decimal has a higher precedence than int, I believe that only one of the constants could be of the type decimal for the return to be of the type decimal also, so that way:

450/4.0

or that:

450.0/4

Should also work.

Browser other questions tagged sql sql-server sql-server-2012

You are not signed in. Login or sign up in order to post.