find month & days between 2 date

[#1]

hello all.

I need to find month & days between 2 dates.

Any suggestion?

Best regards

Muncho

[#2]

Well i tried like this:

div(date2-date1, 30) as months

(date2-date1)-div(date2-date1, 30)*30 as days

this is give me some months & days.

But i like to know is there any other better solution of this?

regards

[#3]

Hi,

Use Interval()

as

Interval ( date2 - date1, 'mm' ) for months

and

Interval ( date2 - date1, 'dd' ) for days

[#4]

don't know, it's not giving me right result.

interval(date2-date1.'mm')  - it's giving me just days.

[#5]

any other suggestion?

[#6]

Hi,

use

SubField(Interval(date2 - date1, 'mm-dd'), '-', 1)

Regards,

Shubhu

[#7]

Hi,

I suggest to split this problem into two steps:

1. Create a calendar

2. Count days and months based on the load statement, like this:

LOAD

     COUNT(DATE)     AS  DAYS,

     COUNT(DISTINCT MONTH)     AS MONTHS

RESIDENT Calendar

WHERE

          (DATE >= '$(dateStart)') AND

           (DATE <= '$(dateEnd)')

;

"dateStart","dateEnd" - variables.

This will give you exactly what you need.

Kind regards,

JTPro