MONTHS BETWEEN UDF [DEPRECATED]

This UDF has been deprecated. Current transformation for Oracle MONTHS_BETWEEN() is Snowflake MONTHS_BETWEEN().

Description

MONTHS_BETWEEN returns number of months between dates date1 and date2. (Oracle MONTHS_BETWEEN SQL Language Reference)

MONTHS_BETWEEN(date1, date2)

Oracle MONTHS_BETWEEN and Snowflake MONTHS_BETWEEN function, have some functional differences, to minimize these differences and replicate Oracle MONTHS_BETWEEN function better, we added a custom UDF.

Custom UDF overloads

MONTHS_BETWEEN_UDF(timestamp_ltz, timestamp_ltz)

Parameters

1. FIRST_DATE: The first TIMESTAMP_LTZ of the operation.

2. SECOND_DATE: The second TIMESTAMP_LTZ of the operation.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.MONTHS_BETWEEN_UDF(FIRST_DATE TIMESTAMP_LTZ, SECOND_DATE TIMESTAMP_LTZ)
RETURNS NUMBER(20,6) IMMUTABLE
AS
$$
CASE WHEN DAY(SECOND_DATE) <= DAY(FIRST_DATE) 
           THEN TIMESTAMPDIFF(MONTH,SECOND_DATE,FIRST_DATE) 
            ELSE TIMESTAMPDIFF(MONTH,SECOND_DATE,FIRST_DATE) - 1 
END + 
(CASE 
    WHEN DAY(SECOND_DATE) = DAY(FIRST_DATE) THEN 0
    WHEN DAY(SECOND_DATE) < DAY(FIRST_DATE) AND TO_TIME(SECOND_DATE) > TO_TIME(FIRST_DATE) THEN DAY(FIRST_DATE) - DAY(SECOND_DATE) - 1
    WHEN DAY(SECOND_DATE) <= DAY(FIRST_DATE) THEN DAY(FIRST_DATE) - DAY(SECOND_DATE) 
    ELSE 31 - DAY(SECOND_DATE) + DAY(FIRST_DATE) 
 END / 31) +
(CASE 
    WHEN DAY(SECOND_DATE) = DAY(FIRST_DATE) THEN 0
    WHEN HOUR(SECOND_DATE) <= HOUR(FIRST_DATE) THEN HOUR(FIRST_DATE) - HOUR(SECOND_DATE) 
    ELSE 24 - HOUR(SECOND_DATE) + HOUR(FIRST_DATE) 
END / (24*31)) +   
(CASE 
     WHEN DAY(SECOND_DATE) = DAY(FIRST_DATE) THEN 0   
     WHEN MINUTE(SECOND_DATE) <= MINUTE(FIRST_DATE) THEN MINUTE(FIRST_DATE) - MINUTE(SECOND_DATE) 
     ELSE 24 - HOUR(SECOND_DATE) + MINUTE(FIRST_DATE) 
        END / (24*60*31)) +
(CASE 
     WHEN DAY(SECOND_DATE) = DAY(FIRST_DATE) THEN 0   
     WHEN MINUTE(SECOND_DATE) <= MINUTE(FIRST_DATE) THEN MINUTE(FIRST_DATE) - MINUTE(SECOND_DATE) 
     ELSE 24 - HOUR(SECOND_DATE) + MINUTE(FIRST_DATE) 
END / (24*60*60*31))
$$
;

Oracle

Query

SELECT 
	MONTHS_BETWEEN('2000-03-20 22:01:11', '1996-03-20 10:01:11'),
	MONTHS_BETWEEN('1996-03-20 22:01:11', '2000-03-20 10:01:11'),
	MONTHS_BETWEEN('1982-05-11 22:31:19', '1900-01-25 15:21:15'),
	MONTHS_BETWEEN('1999-12-25 01:15:16', '1900-12-11 02:05:16')
FROM DUAL;

Result

MONTHS_BETWEEN('2000-03-2022:01:11','1996-03-2010:01:11')|MONTHS_BETWEEN('1996-03-2022:01:11','2000-03-2010:01:11')|MONTHS_BETWEEN('1982-05-1122:31:19','1900-01-2515:21:15')|MONTHS_BETWEEN('1999-12-2501:15:16','1900-12-1102:05:16')|
---------------------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+
                                                       48|                                                      -48|                 987.558021206690561529271206690561529271|                1188.450492831541218637992831541218637993|

Snowflake

Query

SELECT

/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'MONTHS_BETWEEN_UDF' INSERTED. ***/
MONTHS_BETWEEN_UDF('2000-03-20 22:01:11', '1996-03-20 10:01:11'),
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'MONTHS_BETWEEN_UDF' INSERTED. ***/
MONTHS_BETWEEN_UDF('1996-03-20 22:01:11', '2000-03-20 10:01:11'),
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'MONTHS_BETWEEN_UDF' INSERTED. ***/
MONTHS_BETWEEN_UDF('1982-05-11 22:31:19', '1900-01-25 15:21:15'),
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'MONTHS_BETWEEN_UDF' INSERTED. ***/
MONTHS_BETWEEN_UDF('1999-12-25 01:15:16', '1900-12-11 02:05:16')
FROM DUAL;

Result

MONTHS_BETWEEN_UDF('2000-03-20 22:01:11', '1996-03-20 10:01:11')|MONTHS_BETWEEN_UDF('1996-03-20 22:01:11', '2000-03-20 10:01:11')|MONTHS_BETWEEN_UDF('1982-05-11 22:31:19', '1900-01-25 15:21:15')|MONTHS_BETWEEN_UDF('1999-12-25 01:15:16', '1900-12-11 02:05:16')|
----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+
                                                       48.000000|                                                      -48.000000|                                                      987.558024|                                                     1188.450497|

Known Issues

1. Precision may differ from Oracle

Some results may differ in the number of decimal digits.

Related EWIs

1. MSCEWI1020: CUSTOM UDF INSERTED.