"Fowler, Jeff" <[EMAIL PROTECTED]> wrote:
> Hello All,
>
> SQLite newbie here. I've looked through the email archives and website
> trying to find out how to compute the difference in months between two
> given dates. Each date is in YYYY-MM-DD HH:MM:SS format.
>
> The best I've been able to come up with seems rather ugly:
>
> SELECT (strftime( '%Y', date1)*12+strftime('%m',date1)) -
> (strftime('%Y',date1)*12+strftime('%m',date1))
>
> Am I on the right track, or is there something obvious I'm missing?
>
Computing the number of months between two dates is problematic
since the length of a month varies. Is 2007-01-31 to 2007-02-28
a whole month even thought it is only 28 days? What about
2007-04-10 to 2007-05-08? That is also 28 days. Does it count
as a whole month too, or is it two days shy of a whole month?
You can compute the number of days between two days very easily:
SELECT julianday(date2) - julianday(date1);
And I suppose you could divide that value by 30.43666666 (which
is the average number of days in a month) to get the number of
months.
So the answer to your question is that there is no easy answer
to your question because it depends on how you define the
"difference in months" and there does not appear to be a
single intuitive definition for that question.
Do you have a particular algorithm for "difference in months"
in mind?
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
