[Solved] Query to return age as of December 31

Creating tables and queries
Post Reply
dpark29
Posts: 14
Joined: Thu Mar 27, 2014 1:39 pm

[Solved] Query to return age as of December 31

Post by dpark29 »

I am attempting to run a query that will return a person's age on December 31 of the prior year. The following query returns the values I am looking for.

"SELECT ""DOB"", DATEDIFF('yy',""DOB"",'2018-12-31') As ""Age"" FROM ""tblDependents""

To make the query run without editing in future years, I would like to replace the hard-coded '2018-12-31' with a function that returns the last day of the prior year.

I am running Base with the default HSQL database engine.

Thank you for your help.
Last edited by dpark29 on Tue Jan 22, 2019 10:13 pm, edited 1 time in total.
OpenOffice 4.01, Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query to return age as of December 31

Post by FJCC »

Would

Code: Select all

 SELECT year(CURDATE()) - year("DOB") -1 AS "Age" FROM "tblDependents" 
work for you?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
dpark29
Posts: 14
Joined: Thu Mar 27, 2014 1:39 pm

Re: Query to return age as of December 31

Post by dpark29 »

Why yes, that does work! Brilliant...a simple and elegant solution. Thank you.
OpenOffice 4.01, Windows 7
Post Reply