months_between terradata

Analytics
N/A

months_between terradata

I need to calculate the exact months number between two dates

e.g.

as same as ORACLE

select months_between (sysdate, sysdate - 14 ) from dual

Thank you
2 REPLIES
Enthusiast

Re: months_between terradata

You can either join with the calendar tables or play around using the EXTRACT functions to build the logic to do the same in TD.

something on this line .....

SELECT DT1, DT2, ((EXTRACT(YEAR FROM DT1) - EXTRACT(YEAR FROM DT2))*12
+ (EXTRACT(MONTH FROM DT1) - EXTRACT(MONTH FROM DT2)) + (EXTRACT(DAY FROM DT1) - EXTRACT(DAY FROM DT2))/31.0000000) MONTHS_BETWEEN
FROM MYTABLE
;

SELECT DT1, DT2, ((C1.MONTH_OF_CALENDAR - C2.MONTH_OF_CALENDAR) + (C1.DAY_OF_MONTH - C2.DAY_OF_MONTH)/31.0000000) MONTHS_BETWEEN
FROM MYTABLE
INNER JOIN SYS_CALENDAR.CALENDAR C1
ON MYTBL.DT1 = C1.CALENDAR_DATE
INNER JOIN SYS_CALENDAR.CALENDAR C2
ON MYTBL.DT2 = C2.CALENDAR_DATE
;
Enthusiast

Re: months_between terradata

Sylvi,

I think you could spend some time studying the interval datatype, that it is great with date calculations, but unfortunately it is not much intuitive.

One example that might help:

select date - (date - 14) MONTH

This query will return the number of months between the actual date and a date 14 days before, which would be 0 (zero) months.

I hope this helps!