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

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

Known Issues

1. Precision may differ from Oracle

Some results may differ in the number of decimal digits.