11 Replies Latest reply: Jun 19, 2018 8:50 AM by Franz Wiegold

# Extracting months between 2 dates

Hi all,

I'm curious if there's a way I can extract months between two dates... I can get the number of months between 2 dates, but I want to be able to get the actual month names... For example;

Start.Date = 01/01/2017

End.Date = 31/03/2017

Months between two dates = 3.

Now, I want to output those months in for format of the actual month names,so output should be... Jan, Feb, March...

How can I achieve this?

Cheers

• ###### Re: Extracting months between 2 dates

Just to see if i got it,

>= Start Month and  <= End date?

• ###### Re: Extracting months between 2 dates

SQL function on you load script:

datediff(mm,StartDate,EndDate)

And Add 2 to it, right?

• ###### Re: Extracting months between 2 dates

actually use like This:

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

• ###### Re: Extracting months between 2 dates

I believe that will just give the number of months between 2 dates, I am trying to achieve the name of months between 2 dates... As in, Jan, Feb, March etc

• ###### Re: Extracting months between 2 dates

Oh, right, i'm not able to it yet, sorry :C

• ###### Re: Extracting months between 2 dates

I am still trying myself, thanks for your help so far.

• ###### Re: Extracting months between 2 dates

hey there! try this out:

end_date,

month(end_date)-month(start_date) as NofMonths,

month(start_date) as month_start

FROM

months.xlsx

(ooxml, embedded labels, table is Sheet1);

let MonthStart=num(Peek('month_start',0,'months'),00);

let number=Peek('NofMonths',0,'months');

month_names:

LOAD Month(date#(01&num#(\$(MonthStart),00)&2000,'DDMMYYYY')) as month_name AutoGenerate 1;

for i=1 to \$(number)

let vCurMonth=num(\$(MonthStart)+\$(i),00);

Concatenate (month_names)

LOAD Month(date#(01&num#(\$(vCurMonth),00)&2000,'DDMMYYYY')) as month_name AutoGenerate 1;

NEXT i;

• ###### Re: Extracting months between 2 dates

Hey Raffaele,

Thank you that works for extracting the months... Now to connect the remaining months with the month names

• ###### Re: Extracting months between 2 dates

you re welcome

here it is a modified version of the previous script, containing the generalization in case of multiple rows and the connection among the remaining months and month names

months:

end_date,

month(end_date)-month(start_date) as NofMonths,

month(start_date) as month_start,

AutoNumber(start_date&end_date) as key,

RowNo() as rows

FROM

months.xlsx

(ooxml, embedded labels, table is Sheet1);

nrows:

LOAD max(rows) as nrows Resident months;

let nrows=Peek('nrows',0,'nrows');

DROP Table nrows;

for nrow = 0 to \$(nrows)-1

let MonthStart=num(Peek('month_start',\$(nrow),'months'),00);

let vKey=Peek('key',\$(nrow),'months');

let number=Peek('NofMonths',\$(nrow),'months');

month_names:

\$(vKey) as key AutoGenerate 1;

for i=1 to \$(number)

let vCurMonth=num(\$(MonthStart)+\$(i),00);

Concatenate (month_names)

\$(vKey) as key AutoGenerate 1;

NEXT i;

next nrow;

• ###### Re: Extracting months between 2 dates

It only works for the year 2000. what happens when the months are of different year