Extracting months between 2 dates

[Anonymous]

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

[Anonymous]

Just to see if i got it,

>= Start Month and  <= End date?

[Anonymous]

Yes

[Anonymous]

SQL function on you load script:

datediff(mm,StartDate,EndDate)

And Add 2 to it, right?

[Anonymous]

actually use like This:

Add this to your code:

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

Then add, for example:

$(MonthDiff(data_entrega, data_cadastro))+2  as MonthDiff

[Anonymous · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

hey there! try this out:

LOAD start_date,

     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;

[Anonymous]

Hey Raffaele,

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