Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Just to see if i got it,
>= Start Month and <= End date?
Yes
SQL function on you load script:
datediff(mm,StartDate,EndDate)
And Add 2 to it, right?
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
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
Oh, right, i'm not able to it yet, sorry :C
I am still trying myself, thanks for your help so far.
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;
Hey Raffaele,
Thank you that works for extracting the months... Now to connect the remaining months with the month names