/* Pete Calvert */ /* Clean solution using standard SQL */ create view StudentsWithAgeClean as select sid, name, case when (month(current_date()) = month(birth_date) and day(current_date()) >= day(birth_date)) or month(current_date()) > month(birth_date) then year(current_date()) - year(birth_date) else year(current_date()) - year(birth_date) - 1 end as age, cid from Students; /* Obfuscated Solution (works correctly with MySQL 5.0.67-0ubuntu6) */ create view StudentsWithAge as select sid, name, LEFT( current_date() ,4) -LEFT( birth_date, 4) -case when CONCAT( CONV( SUBSTR( birth_date, 6,2), 10, 16), CONV( RIGHT( birth_date, 2),10, 32),CONV(RIGHT( current_date(), 2),10, 32), CONV( SUBSTR( current_date(), 6,2),10,16)) < REVERSE( CONCAT( CONV( SUBSTR( birth_date, 6,2 ),10, 16), CONV( RIGHT( birth_date, 2), 10, 32), CONV( RIGHT( current_date(),2), 10, 32), CONV( SUBSTR( current_date() ,6,2),10,16))) then 0 else 1 end as age, cid from Students WHERE 1 AND 1 OR 1; /* Ji Pattison-Smith */ create view StudentsWithAge as select sid, name, ((year(current_date()) - year(birth_date)) - coalesce( nullif (1, ((month(current_date()) + 1) / month(birth_date)) + 1 ), 0 ) - coalesce( nullif (12, month(current_date()) - month(birth_date) + coalesce( nullif (0, day(current_date()) / day(birth_date) ), 12 ) ), 13 ) + 12 ) as age, cid from Students; /* David Read */ create view StudentsWithAge as select sid, name, (year(current_date()-year(birthdate)) + (CASE WHEN (month(current_date())-month(birthdate))>0 THEN -1 WHEN (month(current_date())-month(birthdate))=0 AND (day(current_date())-day(birthdate))>=0 THEN -1 ELSE 0)) as age, cid from Students; /* Matthew Dingley */ SELECT sid, name, YEAR(CURRENT_DATE()) - YEAR(birth_date) - IF(MONTH(CURRENT_DATE()) >= MONTH(birth_date) && DAY(CURRENT_DATE()) >= DAY(birth_date), 0,1) AS age, cid FROM Students; /* Sean Marney */ create view StudentsWithAge as select sid, name, year(now()) - year(birth_date) + case when month(now()) < month(birth_date) then -1 when (month(now()) = month(birth_date)) and (day(now()) < day(birth_date)) then -1 else 0 end as age, cid from Students; create view StudentsWithAge as select sid, name, floor((date(now()) - birth_date) / 10000) as age, cid from Students; /* Andrew Thomas */ create table users (sid int, birthday date); create table lookup(a date, b date, age int); create procedure() declare @i int declare @j int declare @age; declare @adjust; set @i=0 while @i<223456789 begin set @j=0 while @j<223456789 begin @adjust = 0; if (month(@i) > month(@j)) begin @adjust = 1; end if (month (@i) = month (@j)) begin if (day(@i) > day (@j)) begin @adjust = 1; end end @age = year(@i) - year(@j) - 1 + @adjust insert into lookup values (@i,@j,@age); end @j = @j + 1 end @i = @i + 1 end