URGENT : SELECT ... AS

[Anon]

I am trying to make searches based on the age of a person. The db contains
only the Date of Birth.

So...

My first attempt was to try the following :
(this is all done in the command line on Win2000)

SELECT firstname, surname, dob, floor((to_days(curdate())-to_days(DO😎)/365)
AS age FROM artistes;

This works and returns all the values with the addition of an age column
with what appears to be the correct age.

However...

When I try adding a WHERE clause:

SELECT firstname, surname, dob, floor((to_days(curdate())-to_days(DO😎)/365)
AS age FROM artistes WHERE age = "25";

I get ERROR 1054: Unknown column 'age' in 'where clause'

How am I to use this 'virtual' age column in my searches?

I want to be able to search on exact and age ranges plus display the age in
the returned results.

Any ideas/feedback truly welcome, I am 'under the cosh' to get this done
asap!!! (as usual)

TIA

matt

[sunlight]

I don't know what kind of database system you're using, but you can probably always just do this:

SELECT firstname, surname, dob
WHERE floor((to_days(curdate())-to_days(dob)/365 = 25

There's no need to add 'age' as an extra column, because you already know the answer will always be 25...

Good luck!

Vincent

[[deleted]]

Looks quite MySQL-ish to me, so you could also try the "HAVING" clause:
SELECT floor(bla) as age FROM table HAVING age=25;

But beware, HAVING does not get optimized!

[kparker]

You need to also be aware that the formulaic approach is not 100% accurate during leap years. The extra day will cause it to calculate ages one day early for birthdates after 2/29. As a demonstration:

select to_date('2001-03-01') - to_date('2000-03-01')

gives 365 as a result, whereas

select to_date('2000-03-01') - to_date('1999-03-01')

gives 366.