[Solved] Query to return age as of December 31

Creating tables and queries

[Solved] Query to return age as of December 31

Postby dpark29 » Tue Jan 22, 2019 9:45 pm

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
dpark29
 
Posts: 14
Joined: Thu Mar 27, 2014 1:39 pm

Re: Query to return age as of December 31

Postby FJCC » Tue Jan 22, 2019 10:03 pm

Would
Code: Select all   Expand viewCollapse view
SELECT year(CURDATE()) - year("DOB") -1 AS "Age" FROM "tblDependents"

work for you?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7266
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query to return age as of December 31

Postby dpark29 » Tue Jan 22, 2019 10:13 pm

Why yes, that does work! Brilliant...a simple and elegant solution. Thank you.
OpenOffice 4.01, Windows 7
dpark29
 
Posts: 14
Joined: Thu Mar 27, 2014 1:39 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests