How to achieve rounding upto 2 decimals with padding in hive?

Question

Below is the issue which i want to address, in hive query itself, rather than taking data in POJO and then applying formatting (DefaultFormatter etc.) in my downstream program

hive> SELECT ROUND(0.605,2);
0.61
-- Result      = 0.61
-- Expectation = 0.61

hive> SELECT ROUND(0.604, 2);
0.6
--- Result      = 0.6
--- Expectation = 0.60

how to modify select construct in such a manner which returns decimals with 2 digits padded regardless of their value.

Answer 1

You can use string printf(string format, Obj... args) function, which is based on Formatter.

select printf('%.2f',0.604);

Result:

0.60

You do not need to use round():

select printf('%.2f',0.605);

Result:

0.61