CODEWITHSUNDEEP
sqlrange
daniel
daniel

Reputation: 1

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 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.

Upvotes: 0

Views: 1739

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 1

Andrew Lazarus
Andrew Lazarus

Reputation: 19310

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;

Upvotes: 1

Baaju
Baaju

Reputation: 2010

    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.

Upvotes: 1

Related Questions