Rounded to 4 decimal places, but output shows 4 decimal places 4 0s in MS SQL Server-CodePudding

I am working on Weather Observation Station 17 in HackerRank. Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780. Round your answer to 4 decimal places.

Table: STATION Fields: ID, CITY, STATE, LAT_N, LONG_W where LAT_N is the northern latitude and LONG_W is the western longitude.

My code:

SELECT ROUND(LONG_W,4,0) AS low
FROM STATION
WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

Output: 70.13780000 The answer is wrong. I looked up this question online and the code looks the same in other answers. I am using MS SQL Server. The same code works fine on MySQL

CodePudding user response：

The ROUND function will return the same datatype, precision and scale as the input:

select round(1.10045001, 4); -- 1.10050000
select round(1.10055001, 4); -- 1.10060000

You need CAST(... AS DECIMAL(..., 4)) to generate a decimal with exactly 4 digits. This function will round the value using same algorithm as ROUND during conversion:

select cast(1.10045001 as decimal(18, 4)); -- 1.1005
select cast(1.10055001 as decimal(18, 4)); -- 1.1006

CodePudding user response：

I just wouldn't use ROUND:

SELECT CONVERT(decimal(12,4), LONG_W) AS low
FROM STATION
WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

Also more efficient:

SELECT TOP (1) CONVERT(decimal(12,4), LONG_W) AS low
FROM STATION
WHERE LAT_N > 38.7780
ORDER BY LAT_N;