SQL Statement for querying an age range from a dob

Greetings. I have a table called "persons". It have the dob as a date datatype of course. I cal开发者_运维知识库culate 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.

MySQL doesn't allow the WITH construction. If all you need is the people, and not their exact age at the moment, you can move the test into the WHERE clause.

SELECT nombre, otras_cosas FROM personas 
WHERE NOW() 
 BETWEEN (CAST(fecha_nac) AS DATETIME) + INTERVAL 1 YEAR
   AND (CAST(fecha_nac) AS DATETIME) + INTERVAL 50 YEAR;

There are two ways to do this without temp tables.

One is to repeat the calculation in your where

SELECT yourtable.nombre, yourtable.otras_cosas,
      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   yourtable 
WHERE  Date_format(Now(), '%Y') - Date_format(fecha_nac, '%Y') - ( 
              Date_format(Now(), '00-%m-%d') < Date_format(fecha_nac, '00-%m-%d' 
                                               ) ) BETWEEN 1 AND 50 

The other is to use an inline view

SELECT   t.nombre, t.otras_cosas, t.edad 
FROM   (SELECT  nombre, otras_cosas, 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   yourtable) t 
WHERE  t.edad BETWEEN 1 AND 50