CAST....

Question

Post reply

CAST....

b_boy

SSCertifiable

Points: 5145
More actions
August 18, 2008 at 7:08 am

#191440

Hello
Am running this query:
SELECT T_ShipmentLines.ShipmentLineID, T_ShipmentLines.OrderID, T_OrderHeader.OrderTotal, T_OrderHeader.OrderDate, T_ShipmentLines.ShippedDate,
T_ShipmentLines.ShippedValue, CAST(T_ICPAudit.Amount AS INT) AS Charge,
T_ShipmentLines.ShippedValue - T_ICPAudit.Amount AS [Difference]
FROM T_ShipmentLines INNER JOIN
T_OrderHeader ON T_ShipmentLines.OrderID = T_OrderHeader.OrderID INNER JOIN
T_ICPAudit ON T_ICPAudit.OrderID = T_ShipmentLines.OrderID
ORDER BY T_ShipmentLines.ShippedDate DESC
====
and i get the following error message:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the nvarchar value '59.55' to data type int.
Can anyone help?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rosh-396756 SSCrazy Points: 2338 More actions · Answer 1

I think instead of cast as int you should cast as numeric or decimal.

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 2

I dont think that the problem is due to the CAST done to int because in thins case, you should get String or binary data would be truncated ERROR and not this.

I think the issue is with the columns that you have used in the join. Check whether they are all of the same data type.

John Bonney SSChasing Mays Points: 602 More actions · Answer 3

Rosh's suggestions will work. Or, if T_ICPAudit.Amount is a monetary amount, why not cast it as MONEY?

jritson Ten Centuries Points: 1007 More actions · Answer 4

Hi, I am making an assumption here that cast works the same in 2000 and 2005 so forgive me if I'm wrong, but casting as numeric or decimal will produce a different value to the int function. Int gives you the integer regardless of what's after the decimal, numeric and decimal (no scale or precision) round, so in the case of 59.55 int is 59 and numeric and decimal produce 60.

The reason you're getting the error message is that you are trying to take the integer part of a string. The way to get the 59 in this case (if that's what you want) is to first cast the string as a decimal with at least one decimal place then take the int of that:

cast(cast(T_ICPAudit.Amount as decimal(3,1)) as int)