"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]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
