SQL Server Arithmetic overflow error converting expression to data type datetime

Question

I want to customize my order by , if the Qty is negative then that row must be in fist then if the Qty is positive I will sort it by ExpDate

SELECT WhsCode,
       ItemCode,
       LotNumber,
       ExpDate,
       Qty
FROM rq_Test2
ORDER BY CASE
             WHEN qty < 0 THEN Qty
             ELSE ExpDate
         END

But I am getting an error of " Arithmetic overflow error converting expression to data type datetime. " .. Why ?

Thanks ..

Answer 1

Select WhsCode,ItemCode,LotNumber,ExpDate,Qty 
from rq_Test2 
order by case when qty < 0 then Qty else ExpDate end

Here in case statement compiler will try to convert Qty(numeric value) to Expdate(datetime)

Because case statement will convert all result_expression to the data type with highest precedence in result_expression.

In your case Datetime has higher precedence than numeric or int or Bigint etc.. So you are getting that error.

SQL Server uses the following precedence order for data types:

user-defined data types (highest)
sql_varian t
xml
datetimeoffset
datetime2
datetime
smalldatetime
date
time
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar (including nvarchar(max) )
nchar
varchar (including varchar(max) )
char
varbinary (including varbinary(max) )
binary (lowest)

Answer 2

Have two case statements

Select 
      WhsCode,
      ItemCode,
      LotNumber,
      ExpDate,
      Qty 
from rq_Test2 
order by case when qty < 0 then Qty else null end ,
         case when qty > 0 then ExpDate else NULL end