Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve implicit conversion of data types in SQL Server, concise and easy to understand, absolutely can make your eyes shine, through the detailed introduction of this article I hope you can gain something.

As follows:

CREATE TABLE TEST( ID INT, GOOD_TYPE VARCHAR(12), GOOD_WEIGHT NUMERIC(18,2))INSERT INTO dbo.TESTVALUES( 1, 'T1',1.27) SELECT GOOD_TYPE, CASE WHEN ( GOOD_TYPE = 'T1' ) THEN 99.1 + SUM(GOOD_WEIGHT) ELSE CEILING(SUM(GOOD_WEIGHT)) END AS GrossWeight , SUM(GOOD_WEIGHT) AS NetWeightFROM dbo.TESTGROUP BY GOOD_TYPE;

Why does 99.1 + SUM(GOOD_WEIGHT) become 100? Original SQL is very complex, we analyze, exclude various factors, always get nowhere, all kinds of toss found, if this conversion (see screenshot below), actually OK, after analysis, should be CASE WHEN different data types inside lead to implicit conversion, to be honest before really did not pay attention to CASE WHEN data type implicit conversion, but why must be converted from NUMERIC to INT? Instead of INT implicit conversion to NUMERIC, to be honest, I didn't see the official documentation, if according to the official documentation:

When expressions of two different data types are combined with operators, the lower priority data type is converted to the higher priority data type first. Returns an error if this conversion is not a supported implicit conversion. For operators that combine operand expressions with the same data type, the result of the operation is that data type

And we know that Decimal and NUMERIC are synonyms and are used interchangeably, and the official document "Data Type Priority"(Transact-SQL)", Decimal priority is obviously higher than INT, if you really want to explain according to the principle, should be INT conversion NUMERIC is right (The two data types support implicit conversion), so the more you think about it, the more confused you know, but the real Root Cause is still unclear, and in reports with higher accuracy requirements, this phenomenon will suddenly appear like a Bug. Be careful!

The above is how to implement implicit conversion of data types in SQL Server. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.