Warning - Thar Be Dragons Here! The definition of a month is more or less arbitrary depending on the situation. Trying to define what exactly is the duration of a "month" is a bottomless pit of endless bickering best decided by druids, popes and historians - certainly beyond the scope of SQLite. Some manufacturers even employ 13 "month" years for production planning/scheduling because thirteen 28-day months /almost/ fit into a 365-day year.
There are many other functions with very well-defined/accepted semantics that are more worthy of consideration as a core SQLite function. --- [EMAIL PROTECTED] wrote: > "RB Smissaert" <[EMAIL PROTECTED]> wrote: > > Is there a way to calculate the age given the 2 dates in the standard format > > yyyy-mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide > > by 365, but that won't be accurate enough. > > It would be easy to calculate the age in the application and update the > > database, but I prefer to do it all in SQL if I can. > > > > Suppose there were a MONTHSPAN() function that took two dates and > returns the number of months between them. You could then compute > the age by dividing monthspan() by 12.0 and taking the integer part. > > I took a stab at writing a monthspan() function. Sometimes the > results seem a bit unusual: > > SELECT monthspan('2007-01-01','2006-01-01') -> 12.0 OK > SELECT monthspan('2006-03-01','2006-01-01') -> 2.0 OK > SELECT monthspan('2006-03-31','2006-01-31') -> 2.0 OK > SELECT monthspan('2006-04-01','2006-02-01') -> 2.0 OK > SELECT monthspan('2006-04-30','2006-02-28') -> 2.064516 Hmmmm.... > > The algorithm causes the result to jump up to the next integer > value as you pass the anniversary date of each month. This leads > to some seemingly strange results on shorter months. But I > suppose you are always going to get that when you are trying > to do difference calculations on months of differing lengths. > > Here is the code. I have not checked it in. I'm not sure it > is such a good idea. > > RCS file: /sqlite/sqlite/src/date.c,v > retrieving revision 1.58 > diff -u -r1.58 date.c > --- date.c 25 Sep 2006 18:05:04 -0000 1.58 > +++ date.c 22 Dec 2006 19:25:00 -0000 > @@ -747,6 +747,27 @@ > } > > /* > +** monthspan( TIMESTRING1, TIMESTRING2 ) > +** > +** Return the number of months from the second date to the first > +*/ > +static void monthspanFunc( > + sqlite3_context *context, > + int argc, > + sqlite3_value **argv > +){ > + DateTime date1, date2; > + if( argc==2 && isDate(1, argv, &date1)==0 && isDate(1, &argv[1], > &date2)==0 ) > { > + double m1, m2; > + computeYMD(&date1); > + computeYMD(&date2); > + m1 = date1.Y*12.0 + date1.M + (date1.D-1)/31.0; > + m2 = date2.Y*12.0 + date2.M + (date2.D-1)/31.0; > + sqlite3_result_double(context, m1 - m2); > + } > +} > + > +/* > ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...) > ** > ** Return a string described by FORMAT. Conversions as follows: > @@ -997,6 +1018,7 @@ > { "time", -1, timeFunc }, > { "datetime", -1, datetimeFunc }, > { "strftime", -1, strftimeFunc }, > + { "monthspan", 2, monthspanFunc }, > { "current_time", 0, ctimeFunc }, > { "current_timestamp", 0, ctimestampFunc }, > { "current_date", 0, cdateFunc }, > > -- > D. Richard Hipp <[EMAIL PROTECTED]> __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------