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