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 MONTHS_BETWEEN_UDF(FIRST_DATE TIMESTAMP_LTZ, SECOND_DATE TIMESTAMP_LTZ)
RETURNS NUMBER
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
ROUND(MONTHS_BETWEEN(FIRST_DATE, SECOND_DATE))
$$
;

Oracle

Query

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

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_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.