This article describes how to resolve an error when bigint is converted to int in SQLServer]

One requirement is to store multiple statuses (including exceptions and warning statuses that can exist at the same time) in the status values of one cloudmonitor) the bitwise operation mechanism is used to store data in an int type.

Currently, the monitoring log data volume is very large (hundreds of millions of data records) and data needs to be aggregated hourly and daily for online reports.
The status is divided into three levels: Normal (0), warning (1), and exception (2). max must be used to select the worst state for aggregation, you need to add the level and number of status bits to process the status value. You need to use the bigint type for calculation,

The problem is that when you convert bigint to int to get the original status value, SQLServer reports an error:

Message 8115, level 16, status 2, 1st rows
An arithmetic overflow error occurs when you convert an expression to an int type.

Because 0x80000000 has been used in the status code, the symbol bit problem occurs.

I wrote a conversion function to solve the problem.

The code is as follows:	Copy code
Create function [dbo]. [BigintToInt] ( @ Value bigint ) RETURNS int AS BEGIN -- Whether int symbol bits exist IF @ Value & 0x80000000 <> 0 RETURN @ Value & 0 xFFFFFFFF | 0xffffffffff00000000 -- No sign bit RETURN @ Value & 0 xFFFFFFFF END