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

select floor((months_between(to_date('2022-07-01T00:00:00 05:30'), to_date('2022-01-11T00:00:00 05:30', 'dd-mm-yy'))) /12)

from dual;

Please suggest what I am doing wrong here

CodePudding user response：

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.

select trunc (
  months_between(
    CAST(TO_TIMESTAMP_TZ('2022-07-01T00:00:00 05:30','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS DATE), 
    CAST(TO_TIMESTAMP_TZ('2022-01-11T00:00:00 05:30','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS DATE)
  ) / 12
  , 1
) as result
from dual;

.4

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.

CodePudding user response：

You want:

• to use TO_TIMESTAMP_TZ and not TO_DATE
• to use a format model that matches the timestamp format such as YYYY-MM-DD"T"HH24:MI:SSTZD
• to use FLOOR before dividing by 12 if you want to find the number of full months.

select FLOOR(
         MONTHS_BETWEEN(
           to_timestamp_tz('2022-07-01T00:00:00 05:30', 'YYYY-MM-DD"T"HH24:MI:SSTZD'),
           to_timestamp_tz('2022-01-11T00:00:00 05:30', 'YYYY-MM-DD"T"HH24:MI:SSTZD')
         )
       ) / 12 AS full_months_diff
from   dual;

Which outputs:

FULL_MONTHS_DIFF
.4166666666666666666666666666666666666667

Alternatively, you could use the difference between the timestamps as an INTERVAL YEAR TO MONTH data type:

select EXTRACT(
         YEAR FROM
         ( to_timestamp_tz('2022-07-01T00:00:00 05:30', 'YYYY-MM-DD"T"HH24:MI:SSTZD')
         - to_timestamp_tz('2022-01-11T00:00:00 05:30', 'YYYY-MM-DD"T"HH24:MI:SSTZD')
         ) YEAR TO MONTH
       ) AS years,
       EXTRACT(
         MONTH FROM
         (to_timestamp_tz('2022-07-01T00:00:00 05:30', 'YYYY-MM-DD"T"HH24:MI:SSTZD')
         - to_timestamp_tz('2022-01-11T00:00:00 05:30', 'YYYY-MM-DD"T"HH24:MI:SSTZD')
         ) YEAR TO MONTH
       ) AS months
from   dual;

YEARS	MONTHS
0	6

Which rounds up the number of months.

db<>fiddle here