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]
-----------------------------------------------------------------------------
