Round function - rounding to 0 decimal places

[Vinod]

I am facing a round function related issues while rounding to 0 decimal places:

SELECT ROUND(10.128) FROM DUAL

OutputExpected: 10, OutputActual: 10.0

I also tried SELECT ROUND(10.128, 0) FROM DUAL, but that also gives same result.

Also a multiplication which involves a double, but results into int value, gives a double output.

select 100 * 0.1 from dual

gives output as 10.0 instead of 10.

Is this the desired behavior ?

[Thomas Mueller]

Hi,

ROUND isn't about formatting, it's really only about the mathematical
rounding part.

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/jN85uY5KgWgJ.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

Maybe what you are looking for is CAST:

CAST(10.6 AS BIGINT) or CAST(ROUND(10.6, 0) AS BIGINT)

Regards,
Thomas

[Vinod]

I checked out a few databases and the behavior while rounding using 0 was to remove the decimal part: 

Oracle: http://www.techonthenet.com/oracle/functions/round_nbr.php

PostgreSql: http://www.postgresql.org/docs/8.1/static/functions-math.html

Derby: does not have round function.

Even http://www.w3schools.com/sql/sql_func_round.asp shows rounding using 0 as removing the decimal part.

Are there some databases out there where rounding using 0 expect a decimal part of '.0' ?

>> h2-database+unsubscribe@googlegroups.com.

[Noel Grandin]

You're confusing the documentation with the reality.

You also obviously don't understand the difference between the formatting of data and the actual datatype of the result.

Please go and actually test some other databases to see how they ACTUALLY behave.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/7pU2_4iXrx4J.

To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

It is not the plan to make H2 100% compatible with other databases. I think the current behavior is reasonable and I don't see a reason to change it, except maybe if there is a problem with the data type. H2 currently returns a double, maybe decimal would be better.

Regards,
Thomas