Calculating age using sql

[kenny99076]

Hello again guys,

So I'm wondering how to calculate a persons age? I have a character field (DateOfBirth- stored as YYYMMDD format) and I want to check that against the system date(or hardcode todays date) then put that difference (the age) into my table.

I've tried to use the TO_DATE function as well as cast. But if I do get a query to work my results come out as all ++++++++++.

Here are some examples I've tried:

select ('20120118' - DateOfBirth) as age from jkenny/medoc3

select current_date - to_date(DateOfBirth,'DDMMYY' ) as age from jkenny/medoc3

select to_date(current_date,'DDMMYY') - to_date(DateOfBirth,'DDMMYY') as age from jkenny/medoc3

select current_date - cast(DateOfBirth as date) as age from jkenny/medoc3(this example will give me ++++++++++++ as results.

Any suggestions? I'm running this as a straight query, NOT in an actual program.

[FaStOnE]

Re: Calculating age using sql

Try something like...

Select year( current_date ) - year( Date('1963-03-01') ) as age 
  from sysibm.sysdummy1

[B.Hauser]

Re: Calculating age using sql

If you convert the character date representation into a real date ans substract it from the current_date, you'll get retruned a numeric value representing the date difference formatted as YYYYMMDD --> 330427 means 33 years 4 months 27 days.

To convert a character representation into a real date there are several options:
1. If the character date is formatted as YYYYMMDD you can use:

Date(MyCharDate concat '000000')

2. It is also possible to use Timestamp_Format (syntax alternative TO_DATE), to convert a character string into a TIMESTAMP (not date!)

Timestamp_Format(MyCharDate, 'YYYYMMDD')

you need to specify the CORRECT format. DDMMYY is not the same as DDMMYYYY or MMDDYY or YYYYMMDD!

Select Current_Date - Date(MyCharDate concat '00000') Diff1,
       Current_Date - Timestamp_Format(MyCharDate, 'YYYYMMDD') Diff2
From YourTable

For more information please check the SQL Reference:
SQL Reference

You'll also find some techniques for date and time calulation in the following Redbook Chapter 9. Other considerations:
Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

Birgitta

[kenny99076]

Re: Calculating age using sql

Perfect! Finally got it to work thanks to you guys. Data-types was causing me the grief. I love coming here for help!