Search code examples
sqlsql-servernumbersdecimal

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

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

Solution

  • 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