Calculate Age using Birth dates using SQL 1

[Queryman]

Using just birth dates how do you calculate ages. I have a calculation but it’s rounding to the nearest whole age.

QueryMan

 

[r937]

http://mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#Date_calculations

gives a fabulous formula:

[tt]select ( YEAR(CURRENT_DATE) - YEAR(birth) )
- ( RIGHT(CURRENT_DATE,5) < RIGHT(birth,5) )
as age[/tt]

what's happening here may be hard to see at first

if today's MMDD is less than the birthdate MMDD, then the &quot;less than&quot; evaluates TRUE, which mysql interprets as a 1, which is then subtracted from the difference in years

e.g. if your birthdate is november 26, 1982, then 2002-1982 equals 20, but since 1117 (today) is less than 1126 (birth), you have to subtract 1... so you're 19

the mysql RIGHT function is operating on the YYYYMMDD format of the date as a string

in ansi sql, you could do this:

[tt]select ( YEAR(CURRENT_DATE) - YEAR(birth) )
- ( CASE
WHEN month(CURRENT_DATE) < month(birth)
THEN 1
WHEN month(CURRENT_DATE) = month(birth)
AND day(CURRENT_DATE) < day(birth)
THEN 1
ELSE 0
END ) as age[/tt]

rudy

 

[dnoeth]

As this is an ANSI-SQL forum it's easy ;-)
select
(current_date - birthdate) year

Dieter

 

[Queryman]

select
(current_date - birthdate) year

I get the current_ date and the birthdate, what value is used for year and does this syntax actually work. I tried this, I get a value back for current_date - birthdate, I tried various values for year unsuccesfully, I feel that I am missing something here.
Thanks!

QueryMan

 

[r937]

[tt]select
(current_date - birthdate) year[/tt]

from a standards point of view, the expression subtracting one date or datetime from another must result in a date or datetime interval

thanks for taking us in this direction, dieter

unfortunately, the proof is in the pudding, as the brits are wont to say: each database in the real world has its own way of representing the time interval

in oracle, you get days

which is syntactically a valid representation of someone's age, but semantically, i think the requirement calls for an integer number of years -- at least, that's how most people interpret &quot;nearest whole age&quot;

so be careful, you could be assigning a column alias that says &quot;years&quot; to an expression that contains values in days

far better to use standard sql functions like MONTH and CASE and ensure the results, portable to all databases


rudy

 

[dnoeth]

select
(current_date - birthdate) year

(current_date - birthdate) results in an interval and &quot;year&quot; indicates which type of interval.
But if your DBMS doesn't support Intervals it will probably do a difference in days and use &quot;year&quot; as column alias ;-)
AFAIK only Teradata (fully) and Oracle (partially) and maybe DB2 support Interval expressions.

Another remark, YEAR(current_date)is not ANSI SQL, it's ODBC SQL. In ANSI it's EXTRACT(YEAR FROM current_date)

Dieter

 

[r937]

yes, that's right, it's EXTRACT

i knew that once, but i forgot

sadly, i don't have the ansi spec handy -- no way i'm paying two hunnert bucks for that, eh

what's your favourite resource for checking standard sql?

at the book store, i've flipped through both SQL-99 Complete, Really by Gulutzan and Pelzer, and SQL: The Complete Reference by Groff, Weinberg, and Wald -- but i've never had enough money with me to buy either of them

rudy

 

[dnoeth]

I once had a link to SQL 92 and SQL:1999 [draft] documents, but probably lost it ;-(

Drafts for SQL:2003

http://sqlstandards.org/SC32/WG3/Progression_Documents/Informal_working_drafts

This server is currently down for maintenance

Lots of info about SQL standards

http://www.wiscorp.com/SQLStandards.html

There's a SQL-validator at

http://developer.mimer.com/validator/index.htm

you can pass it every SQL statement and it returns info about standard compliance ;-)

As book about SQL standard i prefere Jim Melton's SQL:1999 and SQL 92.

Dieter