MONTHS_BETWEEN (Lakehouse v1)

Returns the number of months between date1 and date2.

Analyze Syntax

func.months_between(<date1>, <date2>)

Analyze Examples

func.months_between(func.to_date('2024-03-15'), func.to_date('2024-02-15'))
┌───────────────────────────────────────────────────────────────────────────────┐
│ func.months_between(func.to_date('2024-03-15'), func.to_date('2024-02-15'))   │
├───────────────────────────────────────────────────────────────────────────────┤
│ 1                                                                             │
└───────────────────────────────────────────────────────────────────────────────┘

func.months_between(func.to_date('2024-02-15'), func.to_date('2024-03-15'))
┌───────────────────────────────────────────────────────────────────────────────┐
│ func.months_between(func.to_date('2024-02-15'), func.to_date('2024-03-15'))   │
├───────────────────────────────────────────────────────────────────────────────┤
│ -1                                                                             │
└───────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

MONTHS_BETWEEN( <date1>, <date2> )

Arguments

date1 and date2 can be of DATE type, TIMESTAMP type, or a mix of both.

Return Type

The function returns a FLOAT value based on the following rules:

• If date1 is earlier than date2, the function returns a negative value; otherwise, it returns a positive value.

  SELECT
      MONTHS_BETWEEN('2024-03-15'::DATE,
                  '2024-02-15'::DATE),
      MONTHS_BETWEEN('2024-02-15'::DATE,
                  '2024-03-15'::DATE);
  
  -[ RECORD 1 ]-----------------------------------
  months_between('2024-03-15'::date, '2024-02-15'::date): 1
  months_between('2024-02-15'::date, '2024-03-15'::date): -1

• If date1 and date2 fall on the same day of their respective months or both are the last day of their respective months, the result is an integer. Otherwise, the function calculates the fractional portion of the result based on a 31-day month.

  SELECT
      MONTHS_BETWEEN('2024-02-29'::DATE,
                  '2024-01-29'::DATE),
      MONTHS_BETWEEN('2024-02-29'::DATE,
                  '2024-01-31'::DATE);
  
  -[ RECORD 1 ]-----------------------------------
  months_between('2024-02-29'::date, '2024-01-29'::date): 1
  months_between('2024-02-29'::date, '2024-01-31'::date): 1
  
  SELECT
      MONTHS_BETWEEN('2024-08-05'::DATE,
                  '2024-01-01'::DATE);
  
  -[ RECORD 1 ]-----------------------------------
  months_between('2024-08-05'::date, '2024-01-01'::date): 7.129032258064516

• If date1 and date2 are the same date, the function ignores any time components and returns 0.

  SELECT
      MONTHS_BETWEEN('2024-08-05'::DATE,
                  '2024-08-05'::DATE),
      MONTHS_BETWEEN('2024-08-05 02:00:00'::TIMESTAMP,
                  '2024-08-05 01:00:00'::TIMESTAMP);
  
  -[ RECORD 1 ]-----------------------------------
                              months_between('2024-08-05'::date, '2024-08-05'::date): 0
  months_between('2024-08-05 02:00:00'::timestamp, '2024-08-05 01:00:00'::timestamp): 0