Different results with CAST ( )

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Different results with CAST ( )

Author

Topic

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-07-25 : 20:51:37

When I test the CAST function on AdventureWorks2012, the CAST function will round down or up, i.e. 2024.994 to 2025 or 5.1865 to 5.

However, when I try the same the CAST function as:

SELECT CAST(2024.994 AS INT) the result I get is now 2024 (and not 2025).

Why is CAST rounding up when I use it on a table in the AdventureWorks2012 database, but when I use CAST on its own, it rounds down?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-25 : 21:39:11

According to the BOL http://msdn.microsoft.com/en-us/library/ms187928.aspx

quote:

When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded. The following table shows the behavior.

From To Behavior
numeric int Truncate

KH
[spoiler]Time is always against us[/spoiler]

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-25 : 21:51:22

What do you get when you run the following query:
[CODE]

USE AdventureWorks2012;
SELECT TOP 10 LineTotal, CAST(LineTotal AS INT) from Sales.SalesOrderDetail;

Refer to http://msdn.microsoft.com/en-us/library/ms187928.aspx for specifics.
[/CODE]

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-07-26 : 19:22:47

quote:
Originally posted by MuMu88

What do you get when you run the following query:
[CODE]

USE AdventureWorks2012;
SELECT TOP 10 LineTotal, CAST(LineTotal AS INT) from Sales.SalesOrderDetail;

Refer to http://msdn.microsoft.com/en-us/library/ms187928.aspx for specifics.
[/CODE]

Here is what I get:

2024.994000 is 2024
6074.982000 is 6074
2024.994000 is 2024
2039.994000 is 2039

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-26 : 19:46:28

as expected, it is truncating the value.

quote:
When I test the CAST function on AdventureWorks2012, the CAST function will round down or up, i.e. 2024.994 to 2025 or 5.1865 to 5.

can you post the query that you used ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-07-28 : 10:53:00

That is the default behavior of CASTing to INT. When you CAST a value to INT, the decimal part is fully omitted.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources