month difference

[capriconuser]

hi 

there is 2 dates from date and to date .. and i want to get month difference from these two dates 

like if 

from date      to date        month difference

01-02-2019  02-02-2020           13 

 

i tried this but this shows wrong results 

month(from date) - month(to date) 

and i also try this 

month([from date] - [to date]) 

[marcus_malinow]

Ok, an explanation

First off I create a field named IntervalFormatted

This is calculated by taking Date To, formatted as YYYYMMDD, and subtracting Date From, with the same formatting. The result is formatted as '#00000' so we always have characters for YMMDD

For example

20180331 - 20180101 = 00230 (0 years, 02 months, 30 days)

20180312 - 20180115 = 00197

20190319 - 2018-01-20 = 10199

With this result, disregard the right 4 characters (i.e. extract the years) and multiply by 12 to get months

left(IntervalFormatted, len(IntervalFormatted) - 4)

Then take the months part and add these

left(right(IntervalFormatted, 4), 2)

If you still have issues understanding this I'd suggest playing with the code in a demo app. Try adding additional values in the inline table 

[ChennaiahNallani]

Try like below.

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

t:
Load * Inline [
fromdate, todate
01-02-2019, 02-02-2020
07-23-2019, 12-31-2019
07-28-2019, 12-31-2019
07-28-2019, 12-31-2019
08-04-2019, 12-31-2019
08-04-2019, 12-31-2019
08-08-2019, 12-31-2019
];

t1:
Load
date(Date#(fromdate,'MM-DD-YYYY'),'MM-DD-YYYY') as Fromdate1,
date(Date#(todate,'MM-DD-YYYY'),'MM-DD-YYYY') as todate1
Resident t;

Load
Fromdate1 as f,
todate1 as t,
($(MonthDiff(Fromdate1,todate1))) AS Difference
Resident t1;
Drop Table t1;
Drop Table t;