Решение: получение всех дат между двумя датами в SQL

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

Для создания последовательности дат в SQL Server можно использовать рекурсивные общие табличные выражения (CTE). Этот метод последовательно прибавляет к начальной дате один день до тех пор, пока не будет достигнута конечная дата:

DECLARE @Start DATE = '2021-01-01', @End DATE = '2021-01-10';
WITH DateSeq AS (
    SELECT @Start AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date) FROM DateSeq WHERE Date < @End
)
SELECT Date FROM DateSeq OPTION (MAXRECURSION 0);

Данный код выдаст все даты с 1 по 10 января 2021 года включительно.

Повышение производительности за счет операций на основе наборов

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

SELECT CalendarDate
FROM YourCalendarTable
WHERE CalendarDate BETWEEN @Start AND @End;

Если у вас нет доступа к таблице календаря, рекомендуется использовать таблицу чисел или последовательность целых чисел, избегая при этом рекурсивных CTE, которые могут ухудшить производительность.

Эффективное применение большого диапазона дат

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

DECLARE @Start DATE = '2021-01-01', @End DATE = '2021-12-31';
SELECT DATEADD(DAY, nbr – 1, @Start) AS Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY c.object_id) AS nbr
      FROM sys.columns c) numbers
WHERE nbr <= DATEDIFF(DAY, @Start, @End) + 1;

Этот запрос позволяет сгенерировать даты с 1 января по 31 декабря 2021 года, не используя рекурсию, а обращаясь к системным объектам для создания последовательности чисел.

Ясность и простота: наши ключевые союзники

С точки зрения удобства чтения и легкости сопровождения, при наличии возможности откажитесь от сложных рекурсивных CTE в пользу простого запроса SELECT. Для инкапсуляции логики генерации дат используйте табличные функции (TVF), что поможет повторно использовать код.

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

Представим связь между двуми датами в виде моста:

Дата начала 🚩: 2021-01-01 
Дата окончания 🏁: 2021-01-05

Мы строим мост, шаг за шагом:

🌉: [2021-01-01, 2021-01-02, 2021-01-03, 2021-01-04, 2021-01-05]

Каждый шаг моста — это дата в SQL-запросе.

Добавление исключений и настроек в запросы

Избегайте бесконечной рекурсии

При работе с широкими диапазонами дат в рекурсивных CTE установите лимит рекурсии при помощи OPTION (MAXRECURSION 0). Это обеспечит неограниченную рекурсию и тем самым позволит перемещаться во времени без ограничений.

Подробнее об этом расскажет наш спикер на видео

Форматы дат настроиваются под ваши нужды

Для форматирования дат используйте функции CONVERT или FORMAT, чтобы формат вывода дат отвечал вашим предпочтениям:

SELECT FORMAT(Date, 'yyyy-MM-dd') AS FormattedDate
FROM DateSeq; -- Теперь даты отображаются в формате, который вы предпочитаете.

Нерекурсивный подход

Если желательно избежать рекурсии, можете использовать цикл while или таблицу чисел для генерации дат:

DECLARE @DateList TABLE (Date DATE);
DECLARE @Current DATE = @Start;
WHILE @Current <= @End
BEGIN
    INSERT INTO @DateList VALUES (@Current);
    SET @Current = DATEADD(DAY, 1, @Current);
END
SELECT Date FROM @DateList; -- Весь диапазон ваших дат.

Этот цикл проходит по диапазону дат, добавляя каждую дату в таблицу @DateList, что обходит необходимость использовать рекурсивные методы.

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

1. sql – generate days from date range – Stack Overflow — Обсуждение на StackOverflow методов генерации диапазона дат.
2. Creating a date dimension or calendar table in SQL Server — Руководство по созданию таблицы дат или календаря.
3. Date and Time Data Types and Functions – SQL Server (Transact-SQL) | Microsoft Learn — Документация о функциях работы с датой и временем в SQL Server.
4. Using Common Table Expressions | Microsoft Learn — Информация о применении CTE в SQL Server.
5. CREATE FUNCTION (Transact-SQL) – SQL Server | Microsoft Learn — Методическое пособие от Microsoft по созданию табличных функций.
6. Window Functions in SQL Server – Simple Talk — Методика оптимизации SQL-запросов с использованием оконных функций.