You're right of course - I just noticed the question pertains to the
algorithm, not the function itself. Sorry!
I just ran a simple test using "popular RDBMS product A" on one of our
internal databases, as follows:
select 'year difference:', datediff(yy,'12/31/2007','1/1/2008') --> year
difference: 1
select 'month difference:', datediff(mm,'1/31/2007','2/1/2007') -->
month difference: 1
select 'week difference:', datediff(wk,'1/5/2008','1/6/2008') --> week
difference: 1
So for months, the function basically does the arithmetic I did myself
using the
(strftime( '%Y', date1)*12+strftime('%m',date1)) -
(strftime('%Y',date2)*12+strftime('%m',date2))
Regardless of actual days, it simply decides that if two dates occur in
two consecutive calendar months, they are a month apart. Same w/ year
and week. Of course, for precise accuracy it's better to use days..
- Jeff
-----Original Message-----
From: Scott Baker [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 17, 2008 3:13 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date arithmetic question
Fowler, Jeff wrote:
> Yes - I've looked over the current date functions. I would propose a
> single function addition that's hugely valuable in the business world.
> SQL Server has a function called "datediff" for date arithmetic. It
> accepts three parameters. The first indicates the unit of scale
> (years, months, weeks, days, etc. - I think it even goes into
milliseconds!).
> The next two parameters are the dates to compute the difference
between.
> It returns an integer.
I don't think you answered Richard's original question. What constitutes
a month? Since month lengths vary, there is no exactly science to "how
many months between these two dates." Otherwise your best bet is what he
already recommended.
SELECT (julianday(date2) - julianday(date1)) / 30.43666 AS Months;
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
------------------------------------------------------------------------
-----
To unsubscribe, send email to [EMAIL PROTECTED]
------------------------------------------------------------------------
-----
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
