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.
Regards, - Jeff -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 17, 2008 2:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date arithmetic question "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] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
