Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
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
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;