RE: [sqlite] calculate age

[RB Smissaert]

I think I got it now:

select
(date('now') - '2002-12-22') -
        ((julianday('now', '-' ||
        (date('now') - '2002-12-22') ||
        'year') <
                julianday('2002-12-22')))

seems to  work.


RBS

-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 22 December 2006 18:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age

RB Smissaert 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.
>
> RBS
>
>
>
>
----------------------------------------------------------------------------
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
----------------------------------------------------------------------------
-
>
>
>   
Try this (untested).

select
    case
        when date(dob, '+' ||
            strftime('%Y', 'now') - strftime('%Y', dob) ||
            ' years') >= date('now')
        then strftime('%Y', 'now') - strftime('%Y', dob)
        else strftime('%Y', 'now') - strftime('%Y', dob) - 1
    end
    as age
from t;

HTH
Dennis Cote



----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------