Calculate Age in SQL 1

[cantona]

I have the following select statement in Access that i need to convert to a pass through query. How would i convert to oracle sql?

Int((Int("20" & Left([YEAR_CODE],2) & "0831")-Val(Format([DATE_OF_BIRTH],"yyyymmdd")))/10000) AS Age

The year code field is stored as four digits 0099, 0607 etc..

 

[lewisp]

How does the code in YEAR_CODE relate to an actual year? Do you want age according to the current date or according to YEAR_CODE in the database?

 

[cantona]

Hi,

I need it calculating by the year code. So for year code 0607, it would be based on 06.

Thanks

 

[lewisp]

So what does 0099 and 0607 actually mean?

 

[cantona]

Sorry, didnt explain myself very well! Its an academic year code so 0607 would mean 2006/2007. The Access formula is cutting off the 07 to leave 06, meaning 2006. Does that help?

 

[lewisp]

Ok, assuming 0099 should be 9900 and DATE_OF_BIRTH is already a DATE column....

SELECT Floor(Months_Between(To_Date('3108' || Substr(YEAR_CODE,1,2),'DDMMRR'),DATE_OF_BIRTH)/12)
FROM   sys.dual

 

[cantona]

Thanks alot for your help! Thats exactly what i was looking for! Im relatively new to working with Oracle SQL, Could you perhaps explain what each part of the SQL is doing to better help me understand?

Many thanks

 

[lewisp]

[tt]Substr(year_code,1,2)[/tt]: Take two characters of year_code, starting at character 1.

[tt]To_Date('<string>','DDMMRR')[/tt]: Convert string to date, assuming string is in DDMMYY format. RR converts two digit year to a year between 1951 and 2050.

[tt]Months_Between(<date>,<date>)[/tt]: Number of months between two dates. Earliest date first yeilds negative number.

[tt]Floor(<number>)[/tt]: Same as INT. Number rounded down to nearest full number.

 

[lewisp]

Oh, and || means concatenate two strings together to form one string.

 

[cantona]

Thanks, Thats extremely helpful! This should help me out no end in the future!