work out months from dates inputted

[cantlep]

Hi All,

here's my second question for the day.

If I use the date function to select a "start date" and an "end date" for a "new entry" in one of my tables, I'd like something that auto calculates how many months that is in one of my columns. Can anyone help at all?

Cheers

Paul

[shannah]

You might consider using a grafted field to have mysql make the calculation for you.

Code: Select all

__sql__ = "select t.*, datediff(t.end_date,t.start_date) as num_days from mytable t"


This would create a column called num_days. You could estimate number of months from this.

-Steve

[cantlep]

Hi Steve,

Excellent, many thanks. period_diff did the trick with the same code.

Thanks again.

Paul

[cantlep]

I thought period_diff sorted out the months for me but didn't

Is there anything like oracle's months_between() that you know of?

Cheers

[shannah]

I was a little surprised when you said that you got period_diff to work for you. The mysql docs stated that the inputs for period_diff are not dates, but rather are strings of the form 'YYMMDD'.

I didn't see any specific function to give the month diff. My suggestion to do the data_diff would allow you to do a pretty easy calculation of month difference in PHP (e.g. in the fieldname__display() method).