1

I tried this to get our revenue:

SELECT 
    CAST(stays_in_week_nights AS int) + 
        CAST(stays_in_weekend_nights AS int) * 
        CAST(adr AS int) AS revenue
FROM 
    Hotels

I expected to get a number as the result of the calculation - but instead, I get this error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '111.6' to data type int.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lora
  • 25
  • 4
  • 2
    Value `111.6` can't be cast to an integer as it's a decimal. Find that value and correct it or cast to a numeric type that can handle decimals. – JNevill Dec 22 '22 at 15:49
  • You shouldn't be storing numbers as text in the first place. The real bug is using `varchar` instead of eg `numeric(5,2)` or something similar – Panagiotis Kanavos Dec 22 '22 at 16:44

1 Answers1

0

cast is as a decimal instead of as an int. Assuming it is the adr field that is the problem, it would be

SELECT  
   (CAST(stays_in_week_nights AS int) + CAST(stays_in_weekend_nights AS int))
   * CAST(adr AS decimal(10,2)) AS revenue
 FROM Hotels

What you would need for the two parameters in decimal(10,2) would depend on your data. Decimal(10,2) would give you 8 places before the decimal point and 2 after.

Also, you may want to add an extra set of parentheses to make sure you are calculating it correctly. Do you want (week+weekend) * adr or week + (weekend*adr)

What you are currently going to get is the second. Even if that is what you want, adding parentheses would clarify to the next developer that that is what you intended to do

Kevin
  • 7,162
  • 11
  • 46
  • 70