RE: [sqlite] Search on Age, from DOB column

[RB Smissaert]

I am also working with a clinical application, using SQLite and VBA.
I use this function to produce the SQL to convert dates in the  ISO8601
format to an integer age. 

Function ISO8601Date2Age(strField, Optional strAlias As String) As String

   Dim strAS As String

   If Len(strAlias) > 0 Then
      strAS = " AS "
   End If

   ISO8601Date2Age = "case when date(" & strField & ", '+' || " & _
                     "(strftime('%Y', 'now') - strftime('%Y', " & strField &
")) || " & _
                     "' years') <= date('now') then " & _
                     "strftime('%Y', 'now') - strftime('%Y', " & strField &
") " & _
                     "else " & _
                     "strftime('%Y', 'now') - strftime('%Y', " & strField &
") -1  End" & _
                     strAS & strAlias

End Function


You may not be coding in VB, but you will get the idea.

RBS


-----Original Message-----
From: Allan, Mark [mailto:[EMAIL PROTECTED] 
Sent: 03 May 2007 11:57
To: sqlite-users@sqlite.org
Subject: [sqlite] Search on Age, from DOB column

Hi,

I need to be able offer the user the ability to search for patients in the
database based on age. i.e. age > 17 or age = 45 etc etc...

I only store the patient DOB in the database however, what is the SQL to
achive this? Can I subract todays date from the DOB and get the number of
years within an SQL string?

The patient table is similar to:-

Patients
{
        INTEGER PrimaryKey;
        TEXT Surname;
        TEXT FirstName;
        TIMESTAMP DOB;
        ...
        ...
        ...
}


Thanks in advance for your help.

Mark



DISCLAIMER:
This information and any attachments contained in this email message is
intended only for the use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential, and
exempt from disclosure under applicable law.  If the reader of this message
is not the intended recipient, or the employee or agent responsible for
delivering the message to the intended recipient, you are hereby notified
that any dissemination, distribution, forwarding, or copying of this
communication is strictly prohibited.  If you have received this
communication in error, please notify the sender immediately by return
email, and delete the original message immediately.

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




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