RE: [sqlite] calculate age

[RB Smissaert]

Thanks, it is getting close, but it doesn't quite work.

For example this:

select
    case
        when date('2002-12-22', '+' ||
            strftime('%Y', 'now') - strftime('%Y', '2002-12-22') ||
            ' years') >= date('now')
        then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
        else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
    end
    as age

Gives 3

I will try a bit further with my construction.


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