Any function like Oracle "months_between"?

Analytics
Enthusiast

Any function like Oracle "months_between"?

I need to calculate the tenure between two date fields. The result should be number of months.

e.g. case1
Start date: 2006-06-01
End date: 2006-08-01
Result: 2

e.g. case2
Start date: 2006-07-01
End date: 2006-08-01
Result: 1

e.g. case3
Start date: 2006-07-23
End date: 2006-08-01
Result: 1

Any function or method to get this result??

Thanks.

6 REPLIES
Enthusiast

Re: Any function like Oracle "months_between"?

extract(month from end_date) - extract(month from start_date);
Enthusiast

Re: Any function like Oracle "months_between"?

If the dates of tenure are going to span one or more year-ends, then something like this might be quite groovy...

select a.month_of_calendar - b.month_of_calendar
from sys_calendar.calendar a
cross join
sys_calendar.calendar b

where a.calendar_date = latest-date
and b.calendar_date = earliest-date;

Alternatively, if you really want to use a Function, you can get hold of Teradata versions of Oracle MONTH_BETWEEN at Drivers & UDFs on Teradata.com.
Enthusiast

Re: Any function like Oracle "months_between"?

e.g.
start date: 2005-08-01
end date: 2006-07-31
result: 12 (months)

But use your method to calculate the no of months, it would be -1. I expected the result which is 12.
Enthusiast

Re: Any function like Oracle "months_between"?

How about

select extract(month from end_date) - extract(month from start_date)
+ (extract(year from end_date) - extract(year from end_date)) * 12

(Not totally tested, but it seems to work for end_date < start_date and vice versa.)

(By the way, does the third example above match months_between? Wouldn't it return (end date - start date)/31?)

Dave

Re: Any function like Oracle "months_between"?

TRY THIS

cast(((END_DATE - START_DATE ) month(4)) as integer) AS DIFF
Enthusiast

Re: Any function like Oracle "months_between"?

Not best of solution but this SQL seems to work.
I won't recommend this if you can implement UDF instead of this.
If not try this.

SEL
Start_Dte
,End_Dte
,CASE
-- First day of month
WHEN EXTRACT(DAY FROM Start_Dte) = 1 AND EXTRACT(DAY FROM End_Dte) = 1
AND (EXTRACT(YEAR FROM Start_Dte) = EXTRACT(YEAR FROM End_Dte))
THEN EXTRACT(MONTH FROM End_Dte) - EXTRACT(MONTH FROM Start_Dte)

-- Year apart
WHEN EXTRACT(DAY FROM Start_Dte) = 1 AND EXTRACT(DAY FROM End_Dte) = 1
AND ABS(EXTRACT(YEAR FROM Start_Dte) - EXTRACT(YEAR FROM End_Dte) ) = 1
THEN EXTRACT(MONTH FROM End_Dte) + 12 - EXTRACT(MONTH FROM Start_Dte)

-- N year apart
WHEN EXTRACT(DAY FROM Start_Dte) = 1 AND EXTRACT(DAY FROM End_Dte) = 1
AND ABS(EXTRACT(YEAR FROM Start_Dte) - EXTRACT(YEAR FROM End_Dte) ) > 1
THEN EXTRACT(MONTH FROM End_Dte) + 12 - EXTRACT(MONTH FROM Start_Dte) + (EXTRACT(YEAR FROM End_Dte) - EXTRACT(YEAR FROM Start_Dte)-1 ) * 12

-- Any day of month and same year
WHEN EXTRACT(DAY FROM Start_Dte) > 1 AND EXTRACT(DAY FROM End_Dte) = 1
AND ABS(EXTRACT(YEAR FROM Start_Dte) - EXTRACT(YEAR FROM End_Dte) ) = 0
THEN EXTRACT(MONTH FROM End_Dte) + 12 - EXTRACT(MONTH FROM Start_Dte) + (EXTRACT(YEAR FROM End_Dte) - EXTRACT(YEAR FROM Start_Dte)-1 ) * 12

-- First end day of month and different year
WHEN EXTRACT(DAY FROM Start_Dte) > 1 AND EXTRACT(DAY FROM End_Dte) = 1
AND ABS(EXTRACT(YEAR FROM Start_Dte) - EXTRACT(YEAR FROM End_Dte) ) > 0
THEN EXTRACT(MONTH FROM End_Dte) + 12 - (EXTRACT(MONTH FROM Start_Dte)) + (EXTRACT(YEAR FROM End_Dte) - EXTRACT(YEAR FROM Start_Dte)-1 ) * 12

-- Any day of month and different year
WHEN EXTRACT(DAY FROM Start_Dte) > 1 AND EXTRACT(DAY FROM End_Dte) > 1
AND ABS(EXTRACT(YEAR FROM Start_Dte) - EXTRACT(YEAR FROM End_Dte) ) > 0
THEN EXTRACT(MONTH FROM End_Dte) + 12 - (EXTRACT(MONTH FROM Start_Dte)) +1 + (EXTRACT(YEAR FROM End_Dte) - EXTRACT(YEAR FROM Start_Dte)-1 ) * 12

END AS Mth_Diff

FROM test
ORDER BY 1;

Here's the sample result

start_Dte end_dte Mth_Diff
07/23/2000 08/01/2006 73
05/03/2002 03/31/2006 47
07/01/2003 08/01/2006 37
06/01/2004 08/01/2006 26
05/01/2005 08/01/2006 15
06/01/2006 08/01/2006 2
07/01/2006 08/01/2006 1
07/23/2006 08/01/2006 1

Vinay