Как найти возраст в sql

Как вычислить полный возраст по дате рождения?

Есть таблица

Делаю запрос

SELECT id, birthday, DATE_FORMAT( FROM_DAYS( DATEDIFF(str_to_date('14.09.20', '%d.%m.%y'), str_to_date(birthday, '%d.%m.%y')) ), '%y Years %m Months %d Days' ) AS age FROM children LIMIT 15;

Результат

Почему некоторые записи возраст выходит 0 ? Что не так ?
update:

SELECT id, birthday, str_to_date(birthday, '%d.%m.%y') as born, DATE_FORMAT( FROM_DAYS( DATEDIFF(str_to_date('14.09.20', '%d.%m.%y'), str_to_date(birthday, '%d.%m.%y')) ), '%y Years %m Months %d Days' ) AS age FROM children LIMIT 15;

Вроде дата парситься норм.

• Вопрос задан более трёх лет назад
• 1782 просмотра

3 комментария

Средний 3 комментария

Как найти возраст в sql

В MySQL имеется несколько функций, реализующих арифметические операции над датами. Эти функции позволяют, например, вычислять возраст или получать части даты.

Определить возраст любого из животных в базе можно, если вычислить разницу между текущим годом и годом его рождения, а из результата вычесть единицу, если текущий день находится к началу календаря ближе, нежели день рождения животного. Приведенный ниже запрос выводит дату рождения каждого животного, его возраст и текущую дату.

mysql> SELECT name, birth, CURRENT_DATE, -> (YEAR(CURRENT_DATE)-YEAR(birth)) -> - (RIGHT(CURRENT_DATE,5) AS age -> FROM pet; +----------+------------+--------------+------+ | name | birth | CURRENT_DATE | age | +----------+------------+--------------+------+ | Fluffy | 1993-02-04 | 2001-08-29 | 8 | | Claws | 1994-03-17 | 2001-08-29 | 7 | | Buffy | 1989-05-13 | 2001-08-29 | 12 | | Fang | 1990-08-27 | 2001-08-29 | 11 | | Bowser | 1989-08-31 | 2001-08-29 | 11 | | Chirpy | 1998-09-11 | 2001-08-29 | 2 | | Whistler | 1997-12-09 | 2001-08-29 | 3 | | Slim | 1996-04-29 | 2001-08-29 | 5 | | Puffball | 1999-03-30 | 2001-08-29 | 2 | +----------+------------+--------------+------+

В этом примере функция YEAR() выделяет из даты год, а RIGHT() — пять крайних справа символов, представляющих календарный день (MM-DD). Часть выражения, сравнивающая даты, выдает 1 или 0, что позволяет уменьшить результат на единицу, если текущий день ( CURRENT_DATE ) находится к началу календаря ближе, нежели день рождения животного. Все выражение смотрится несколько неуклюже, поэтому вместо него в заголовке соответствующего столбца результатов выводится псевдоним ( age — «возраст»).

Запрос неплохо работает, но разобраться в результатах было бы проще, если бы строки располагались в определенном порядке. Этого можно достичь, добавив в запрос выражение ORDER BY name и отсортировав таким образом результаты по имени:

mysql> SELECT name, birth, CURRENT_DATE, -> (YEAR(CURRENT_DATE)-YEAR(birth)) -> - (RIGHT(CURRENT_DATE,5) AS age -> FROM pet ORDER BY name; +----------+------------+--------------+------+ | name | birth | CURRENT_DATE | age | +----------+------------+--------------+------+ | Bowser | 1989-08-31 | 2001-08-29 | 11 | | Buffy | 1989-05-13 | 2001-08-29 | 12 | | Chirpy | 1998-09-11 | 2001-08-29 | 2 | | Claws | 1994-03-17 | 2001-08-29 | 7 | | Fang | 1990-08-27 | 2001-08-29 | 11 | | Fluffy | 1993-02-04 | 2001-08-29 | 8 | | Puffball | 1999-03-30 | 2001-08-29 | 2 | | Slim | 1996-04-29 | 2001-08-29 | 5 | | Whistler | 1997-12-09 | 2001-08-29 | 3 | +----------+------------+--------------+------+

Отсортировать результаты по возрасту также можно при помощи выражения ORDER BY :

mysql> SELECT name, birth, CURRENT_DATE, -> (YEAR(CURRENT_DATE)-YEAR(birth)) -> - (RIGHT(CURRENT_DATE,5) AS age -> FROM pet ORDER BY age; +----------+------------+--------------+------+ | name | birth | CURRENT_DATE | age | +----------+------------+--------------+------+ | Chirpy | 1998-09-11 | 2001-08-29 | 2 | | Puffball | 1999-03-30 | 2001-08-29 | 2 | | Whistler | 1997-12-09 | 2001-08-29 | 3 | | Slim | 1996-04-29 | 2001-08-29 | 5 | | Claws | 1994-03-17 | 2001-08-29 | 7 | | Fluffy | 1993-02-04 | 2001-08-29 | 8 | | Fang | 1990-08-27 | 2001-08-29 | 11 | | Bowser | 1989-08-31 | 2001-08-29 | 11 | | Buffy | 1989-05-13 | 2001-08-29 | 12 | +----------+------------+--------------+------+

подобный же запрос поможет определить возраст, которого достигли умершие животные на момент смерти. Выделить умерших животных можно, проверив значение поля death на предмет равенства NULL . Затем для записей, значения поля death которых не равно NULL , можно вычислить разницу между датами смерти и рождения:

mysql> SELECT name, birth, death, -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5) AS age -> FROM pet WHERE death IS NOT NULL ORDER BY age; +--------+------------+------------+------+ | name | birth | death | age | +--------+------------+------------+------+ | Bowser | 1989-08-31 | 1995-07-29 | 5 | +--------+------------+------------+------+

В этом запросе используется выражение death IS NOT NULL , а не death <> NULL , так как NULL — особое значение (более подробные пояснения приведены в разделе see section 3.3.4.6 Работа с значениями NULL).

А как поступать, если потребуется определить, дни рождения каких животных наступят в следующем месяце? Для таких расчетов день и год значения не имеют; из столбца, содержащего дату рождения, нас интересует только месяц. В MySQL предусмотрено несколько функций для получения частей дат — YEAR() , MONTH() , и DAYOFMONTH() . В данном случае нам подойдет функция MONTH() . Увидеть работу этой функции можно с помощью простого запроса, выводящего дату рождения birth и MONTH(birth) :

mysql> SELECT name, birth, MONTH(birth) FROM pet; +----------+------------+--------------+ | name | birth | MONTH(birth) | +----------+------------+--------------+ | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Fang | 1990-08-27 | 8 | | Bowser | 1989-08-31 | 8 | | Chirpy | 1998-09-11 | 9 | | Whistler | 1997-12-09 | 12 | | Slim | 1996-04-29 | 4 | | Puffball | 1999-03-30 | 3 | +----------+------------+--------------+

Найти животных, дни рождения которых наступят в следующем месяце, тоже несложно. Предположим, что сейчас на дворе апрель. Тогда номер текущего месяца — 4, а искать надо животных, родившихся в мае (5-м месяце), таким образом:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; +-------+------------+ | name | birth | +-------+------------+ | Buffy | 1989-05-13 | +-------+------------+

Конечно, в декабре возникают некоторые осложнения. Если просто добавить единицу к номеру месяца (12) и поискать животных, родившихся в тринадцатом месяце, найти что-нибудь вряд ли удастся. Вместо этого нужно искать животных, родившихся в январе (1-м месяце).

Можно даже написать небольшой запрос, который будет работать вне зависимости от того, какой нынче месяц. Функция DATE_ADD() позволяет прибавить к дате некоторый интервал времени. Если добавить к значению, возвращаемому функцией NOW() , месяц, а затем извлечь из получившейся даты номер месяца при помощи функции MONTH() , мы получим именно тот месяц, который нам нужен:

mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

Ту же задачу можно решить и другим методом — для этого нужно прибавить единицу к номеру месяца, следующего за текущим (воспользовавшись функцией расчета по модулю (MOD) для перехода к 0, если номер текущего месяца равен 12):

mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

Функция MONTH возвращает число от 1 до 12, а выражение MOD(число,12) — число от 0 до 11. Поэтому операцию сложения нужно проводить после MOD() , иначе результат перепрыгнет с ноября (11) сразу на январь (1).

SQL → Вычислить возраст по дате рождения

Более универсальный вариант SQL запроса из официальной документации:

SELECT birthday, ( (YEAR(CURRENT_DATE) - YEAR(birthday)) - /* step 1 */ (DATE_FORMAT(CURRENT_DATE, '%m%d') < DATE_FORMAT(birthday, '%m%d')) /* step 2 */ ) AS age FROM users

Смысл следующий — первым шагом вычитаем из текущего года, год рождения, вторым шагом вычитаем единичку если дня рождения в этом году ещё не было.

birthday это поле с датой в любом валидном формате (не timestamp), например 1989-07-22 или даже 1989-07-22T00:00:00 .

Похожие записи

• Экспорт/импорт большой базы
• Узнать следующую и предыдущую запись в выборке по полю с неуникальными значениями
• Избавляемся от ошибки "MySQL server has gone away"
• Выборка постов с определёнными тегами
• Выборка людей определённого возраста

Пользовательские функции и процедуры MS SQL

MS SQL user function, procedures – Пользовательские функции и процедуры MS SQL.

Полный возраст в MS SQL.

В MS SQL Server есть очень удобная и замечательная встроенная функция datediff, которая возвращает разницу между двумя датами. Например, следующий запрос вернет разницу в годах между 31-м декабря 2014 года и 1-м января 2015 года:

select datediff(year, '2014-12-31', '2015-01-01')

Результат запроса будет равен 1, хотя разница между двумя всего 1 день. Дело в том, что datediff возвращает количество пересеченных границ указанных аргументом datepart, за период времени между аргументами startdate и enddate функции.
Такой результат не очень устраивает нас, если мы хотим определить количество полных лет между двумя датами, т.е. возраст на определенную дату. Немного дополним вывод этой функции и оформим в виде функции:

create function [dbo].fullAge (@startdate datetime , @enddate datetime) returns int as begin return (datediff(year, @startdate, @enddate) - case when month(@startdate) < month(@enddate) then 0 when month(@startdate) >month(@enddate) then 1 when day(@startdate) > day(@enddate) then 1 else 0 end) end

Здесь вначале находится разница в годах между двумя календарными датами функцией datediff и от неё вычитается 1, если в календарном году даты @enddate месяц и дата переменной @startdate еще не наступили относительно переменной @enddate.