|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/17/2007 5:07:00 AM
Posts: 1,
Visits: 1
|
|
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
sylvi
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
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
;
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 11/27/2008 9:25:22 AM
Posts: 43,
Visits: 51
|
|
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!
|
|
|
|