SQL statement to query an age range from a dob

advertisements

Greetings. I have a table called "persons". It have the dob as a date datatype of course. I calculate the age with the following statement:

SELECT DATE_FORMAT( NOW( ) ,  '%Y' ) - DATE_FORMAT( fecha_nac,  '%Y' ) -
( DATE_FORMAT(NOW( ) ,  '00-%m-%d' ) < DATE_FORMAT( fecha_nac,  '00-%m-%d' ) )
AS edad

But when I try to query a specific age with the following statement it gives me errors:

SELECT DATE_FORMAT( NOW( ) ,  '%Y' ) - DATE_FORMAT( fecha_nac,  '%Y' ) -
( DATE_FORMAT( NOW( ) ,  '00-%m-%d' ) < DATE_FORMAT( fecha_nac,  '00-%m-%d' ) )
AS edad WHERE edad BETWEEN 1 AND 50

Of course it gives me erros because the "edad" column doesn't exists. I need a sql query to list all the people within an age range from a dob. I don't know how to make this query please help.


    WITH TBL AS
    (
    SELECT DATE_FORMAT( NOW( ) ,  '%Y' ) - DATE_FORMAT( fecha_nac,  '%Y' ) - ( DATE_FORMAT( NOW( ) ,  '00-%m-%d' ) < DATE_FORMAT( fecha_nac,  '00-%m-%d' ) ) AS edad
FROM Persons
    )

    SELECT Edad FROM TBL WHERE Edad BETWEEN 1 AND 50

The above is SQL Server. You can use Temp Tables to accomplish the same thing in other databases as well.