Solved: floor while calculating between two dates is giving 0 in Oracle


I have two dates by which I am calculating no of years/months. For below 2 dates I am getting output as 0 as it should return 0.4 months.
Here is my query
from dual;
Please suggest what I am doing wrong here

Best Answer:

The floor function:

returns the largest integer equal to or less than n

so there is no way it can return 0.4. The ceil function is the similar. Neither takes an argument allowing retention of decimal places. And you don’t want to round it, as in your example that would give 0.5, not 0.4.
Fortunately you can use trunc, which does have a decimal-place argument:

The TRUNC (number) function returns n1 truncated to n2 decimal places.

So you want trunc(<difference between dates>, 1) to get retain 1 decimal place.
Here trunc behaves essentially as you would want floor(n1, n2) to if that existed; there is no equivalent for ceil, but you can work around that. The same method can be applied here too, but isn’t needed; I’ve included it in this db<>fiddle for fun.

If you have better answer, please add a comment about this, thank you!