Geek Answers Handbook

SQL Server Overflow arithmetic error converting expression to datetime data type

I want to adjust my order, if Qty is negative, then this line should be in a fist, and if Qty is positive, I will sort it by ExpDate

My request is this.

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

But I get the error "Overflow arithmetic error converting expression to datetime data type." Why?

Thank..

+4
source share
2 answers

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
+1
source 
Select WhsCode,ItemCode,LotNumber,ExpDate,Qty 
from rq_Test2 
order by case when qty < 0 then Qty else ExpDate end

case Qty(numeric value) Expdate(datetime)

case result_expression result_expression.

Datetime , numeric or int or Bigint etc.. , .

SQL Server precedence :

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)
+2

More articles:


All Articles