DATEDIFF: примеры (SQL)

Базовые примеры использования

Примеры демонстрируют использование различных параметров datepart.

SELECT DATEDIFF(day, '2023-01-01', '2023-01-10');

9

SELECT DATEDIFF(month, '2023-03-15', '2023-07-20');

4

SELECT DATEDIFF(year, '1999-12-31', '2023-01-01');

24

SELECT DATEDIFF(minute, '09:00:00', '13:30:00');

270

-- Отрицательный результат при обратном порядке дат
SELECT DATEDIFF(week, '2023-12-01', '2023-11-01');

-4

Похожие функции в MS SQL

В MS SQL существуют другие функции для работы с датами.

• DATEADD — добавляет указанный интервал к дате. Используется, когда к исходной дате нужно прибавить или вычесть период.
• DATEDIFF_BIG — аналогична DATEDIFF, но возвращает значение типа bigint. Предпочтительна при расчете очень больших интервалов в мелких единицах (например, микросекунды за несколько лет).
• DATEDIFF_PARTS (доступна с SQL Server 2022) — возвращает разницу между датами в виде строки, разбитой на составляющие (годы, месяцы, дни и т.д.). Полезна для получения полного разложения интервала.

Типичные ошибки

Распространенные ошибки связаны с непониманием типа возвращаемого значения и границ расчета.

Ошибка 1: Ожидание дробного результата при разнице в месяцах или годах. Функция возвращает только целое количество пересеченных границ указанных единиц.

-- Разница между 31 января и 1 февраля в месяцах
SELECT DATEDIFF(month, '2023-01-31', '2023-02-01');

1

Ошибка 2: Передача строк в неоднозначном формате, что может привести к ошибке конвертации или неверному результату из-за региональных настроек.

-- Рискованно, зависит от настроек языка
SELECT DATEDIFF(day, '01/02/2023', '2023-03-01');

Может привести к ошибке 'Conversion failed' или неожиданному результату.

Ошибка 3: Использование неподдерживаемого значения для datepart.

SELECT DATEDIFF(decade, '2000-01-01', '2020-01-01');

Сообщение об ошибке: 'decade' is not a recognized datepart option.

Изменения в последних версиях

В SQL Server 2022 была добавлена новая функция DATEDIFF_PARTS, которая дополняет возможности DATEDIFF. Она возвращает строку, содержащую разницу между двумя датами, разбитую на отдельные компоненты (годы, месяцы, дни, и т.д.). Это позволяет получить более детализированный интервал, чем одно целое число от DATEDIFF.

-- Только для SQL Server 2022 и выше
SELECT DATEDIFF_PARTS('2020-05-15', '2023-08-20');

3 years, 3 months, 5 days

Расширенные и нестандартные примеры

Примеры показывают практическое применение функции в сложных сценариях.

Расчет возраста с точностью до дня: Комбинация нескольких вызовов DATEDIFF для учета прошедших дней после последнего дня рождения.

DECLARE @BirthDate DATE = '1990-05-15';
DECLARE @CurrentDate DATE = GETDATE();
SELECT
    DATEDIFF(year, @BirthDate, @CurrentDate) -
    CASE WHEN DATEADD(year, DATEDIFF(year, @BirthDate, @CurrentDate), @BirthDate) > @CurrentDate
         THEN 1 ELSE 0 END AS FullYears,
    DATEDIFF(day,
             DATEADD(year, DATEDIFF(year, @BirthDate, @CurrentDate) -
                    CASE WHEN DATEADD(year, DATEDIFF(year, @BirthDate, @CurrentDate), @BirthDate) > @CurrentDate
                         THEN 1 ELSE 0 END, @BirthDate),
             @CurrentDate) AS DaysAfterLastBirthday;

-- Результат зависит от текущей даты, например: 33 | 215

Определение количества рабочих дней (понедельник-пятница) между датами: Используется для исключения выходных.

DECLARE @Start DATE = '2023-10-01', @End DATE = '2023-10-31';
SELECT
    DATEDIFF(day, @Start, @End) + 1 -
    (DATEDIFF(week, @Start, @End) * 2) -
    CASE WHEN DATEPART(weekday, @Start) = 1 THEN 1 ELSE 0 END -
    CASE WHEN DATEPART(weekday, @End) = 7 THEN 1 ELSE 0 END AS WorkDays;

22

Разбивка интервала на составляющие (без DATEDIFF_PARTS): Эмуляция разницы в годах, месяцах и днях.

DECLARE @d1 DATE = '2018-03-25', @d2 DATE = '2023-11-08';
SELECT
    DATEDIFF(year, @d1, @d2) -
        CASE WHEN DATEADD(year, DATEDIFF(year, @d1, @d2), @d1) > @d2 THEN 1 ELSE 0 END AS years,
    DATEDIFF(month, DATEADD(year,
        DATEDIFF(year, @d1, @d2) -
        CASE WHEN DATEADD(year, DATEDIFF(year, @d1, @d2), @d1) > @d2 THEN 1 ELSE 0 END, @d1), @d2) -
        CASE WHEN DAY(@d1) > DAY(@d2) THEN 1 ELSE 0 END AS months,
    @d2 - DATEADD(month,
        DATEDIFF(month, DATEADD(year,
            DATEDIFF(year, @d1, @d2) -
            CASE WHEN DATEADD(year, DATEDIFF(year, @d1, @d2), @d1) > @d2 THEN 1 ELSE 0 END, @d1), @d2) -
            CASE WHEN DAY(@d1) > DAY(@d2) THEN 1 ELSE 0 END,
        DATEADD(year, DATEDIFF(year, @d1, @d2) -
            CASE WHEN DATEADD(year, DATEDIFF(year, @d1, @d2), @d1) > @d2 THEN 1 ELSE 0 END, @d1)) AS days;

5 | 7 | 14

Расчет микросекунд для высокоточных измерений: Использование типа datetime2.

SELECT DATEDIFF(microsecond, '2023-01-01 00:00:00.0000000', '2023-01-01 00:00:00.1234567');

1234567

Аналоги функции в других СУБД

В других базах данных существуют похожие, но синтаксически отличающиеся функции.

MySQL: Функция DATEDIFF вычисляет разницу только в днях, принимая два аргумента.

SELECT DATEDIFF('2023-01-10', '2023-01-01');

9

Oracle: Используется оператор вычитания дат для получения разницы в днях. Для других интервалов применяется EXTRACT или функции типа MONTHS_BETWEEN.

SELECT DATE '2023-01-10' - DATE '2023-01-01' FROM dual;

9

PostgreSQL: Оператор вычитания дат возвращает интервал. Для получения конкретных единиц используется EXTRACT или AGE.

SELECT EXTRACT(DAY FROM DATE '2023-01-10' - DATE '2023-01-01');

9

SQLite: Нет встроенной функции DATEDIFF. Используются строковые функции или вычисления через julianday.

SELECT julianday('2023-01-10') - julianday('2023-01-01');

9.0