Расчёт возраста по дате рождения в SQL: преобразование и вычисления

Быстрый ответ

Для точного расчета возраста в SQL применяем функцию DATEDIFF, используя ее вместе с оператором CASE. Этот подход позволяет учесть, был ли день рождения в текущем году:

SELECT 
  DATEDIFF(YEAR, DOB, GETDATE()) – CASE 
    WHEN (MONTH(DOB) > MONTH(GETDATE())) OR 
         (MONTH(DOB) = MONTH(GETDATE()) AND DAY(DOB) > DAY(GETDATE())) 
    THEN 1 
    ELSE 0 
  END AS Age 
FROM 
  YourTable;

Таким образом, если по сегодняшнюю дату день рождения еще не наступил, метод корректно уменьшает возраст на один год.

Подготовка формата DateTime

Перед выполнением расчетов необходимо привести поле DOB к формату datetime. Это обеспечит стандартизацию и точность вычислений:

SELECT CONVERT(datetime, DOB, 112) as FormattedDOB
FROM YourTable;

Дата рождения тут преобразуется в формат (ГГГГММДД).

Учёт високосных годов

Кроме того, мы принимаем во внимание високосные годы. Их влияние учитывается путем использования в знаменателе числа 365,25 дней, чтобы учесть дополнительный день каждые четыре года:

SELECT FLOOR(DATEDIFF(DAY, DOB, GETDATE()) / 365.25) AS Age
FROM YourTable;

Благодаря этой формуле, дополнительный день в високосных годах не упускается из расчетов.

Особенность: день рождения 29 февраля

Если день рождения приходится на 29 февраля, возраст следует считать, исходя из даты 28 февраля:

SELECT 
  CASE 
    WHEN DOB = '2000-02-29' AND MONTH(GETDATE()) > 2 THEN 
      FLOOR(DATEDIFF(DAY, DATEADD(DAY, -1, DOB), GETDATE()) / 365.25)
    ELSE
      FLOOR(DATEDIFF(DAY, DOB, GETDATE()) / 365.25)
  END AS Age
FROM YourTable;

Таким образом, мы корректно учитываем возраст тех, чей день рождения настоящим праздником становится раз в четыре года!

Создание переиспользуемой функции

Можно создать SQL-функцию, которую затем можно будет использовать многократно:

CREATE FUNCTION dbo.CalculateAge (@DOB datetime)
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
RETURN DATEDIFF(YEAR, @DOB, GETDATE()) – CASE WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR (MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END;
END;

Теперь возраст можно вычислить с помощью одной функции:

SELECT dbo.CalculateAge(DOB) AS Age
FROM YourTable;

Даже старые функции способны на новые трюки!

Визуализация

Визуализируем процесс определения возраста. Вот наглядная схема:

Текущая дата: 🗓️ (На эту дату указывает `getDate()`)
Дата рождения: 🎂

Аналогично тому, как мы добавляем свечи на торт, базы данных считают годы:

SELECT FLOOR(DATEDIFF(day, 🎂, 🗓️) / 365.25) AS AgeInYears;

Можно считать это своеобразным вкладом Stack Overflow в праздник:

| Год        | Лет    |
| ---------- | ----------------- |
| Год 🎂     | 1                 |
| Год спустя | 2                 |
| ...        | ...               |
| 🗓️ Год    | 🎈🎈🎈            |

Таким образом, SQL тоже умеет праздновать дни рождения – запросами.

Работа с различными форматами дат

Иногда DOB представлена в нестандартном формате. Можно исправить это, преобразовав дату для расчета возраста:

SELECT 
  DATEDIFF(YEAR, CONVERT(datetime, DOB, 112), GETDATE()) -
  CASE 
    WHEN (MONTH(CONVERT(datetime, DOB, 112)) > MONTH(GETDATE())) OR 
         (MONTH(CONVERT(datetime, DOB, 112)) = MONTH(GETDATE()) AND DAY(CONVERT(datetime, DOB, 112)) > DAY(GETDATE())) 
    THEN 1 
    ELSE 0 
  END AS Age 
FROM YourTable;

Это все равно, что приводить торт в порядок с помощью подходящего инструмента!

Тестирование граничных случаев

Проверьте надежность своего решения, используя тестовые примеры, особенно относящиеся к високосным годам или возрасту новорожденных:

-- Тестируем функцию на крайние даты рождения
WITH SampleDates AS (
  SELECT CAST('2000-02-29' AS datetime) AS DOB -- Родился в високосный день, редкость.
  UNION ALL SELECT CAST('2001-12-31' AS datetime) -- Малыш, родившийся в канун Нового Года!
  UNION ALL SELECT CAST('1995-01-01' AS datetime) -- Новогодний малыш!
)
SELECT DOB, dbo.CalculateAge(DOB) AS CalculatedAge
FROM SampleDates;

Тестирование – это как шоколадка на торте: с ним все еще лучше!

Простой подход

Иногда достаточно знать количество Полных Лет:

SELECT 
  (CAST(CONVERT(varchar, GETDATE(), 112) AS INT) – 
   CAST(CONVERT(varchar, DOB, 112) AS INT)) / 10000 AS Age
FROM YourTable;

Как и в жизни, иногда простота – это основная красота.

Полезные материалы

1. Как рассчитать возраст (в годах) на основе даты рождения и getDate() – Stack Overflow — Обсуждение на Stack Overflow вычисления возраста.
2. Типы данных и функции даты и времени – SQL Server (Transact-SQL) | Microsoft Learn — Официальная документация Microsoft по функциям работы с датой и временем.
3. Как рассчитать возраст в T-SQL с учетом лет, месяцев и дней – Stack Overflow — Еще одна тема на Stack Overflow, где обсуждаются сложные расчеты возраста с учетом лет, месяцев и дней.
4. Создание таблицы размерности дат или календарной таблицы в SQL Server — Руководство на MSSQLTips, объясняющее создание таблицы размерности дат или календарной таблицы в SQL Server.
5. GETDATE() – Форумы SQL Server Central — Активное обсуждение использования функции GETDATE().
6. SQL Server: Функция DATEDIFF — Подробное описание функции DATEDIFF в SQL Server, которая имеет ключевое значение для определения возраста.