Как получить последовательность дат в указанном промежутке на T-SQL

Всем привет! Сегодня мы поговорим о том, как на языке T-SQL можно сформировать последовательность дат в указанном диапазоне, т.е. когда требуется получить все даты между двумя определенными датами, при этом чтобы каждое значение даты в результирующем наборе данных было в отдельной строке.

Заметка! Чем отличается инструкция THROW от RAISERROR в T-SQL.

Допустим, Вам требуется вывести все даты, начиная с 01.01.2020 по 12.01.2020, иными словами, Вам необходимо сформировать следующую таблицу.

И первое, что может прийти в голову, это использовать или оператор UNION, или конструктор табличных значений VALUES, и в этом конкретном случае, когда требуется сформировать всего 12 записей, это может показаться достаточно простой задачей, однако представим, что нам требуется сформировать даты за большой промежуток времени, например за год, или за несколько лет, тогда этот способ сразу отпадает, так как вручную формировать тысячи строк, наверное, как минимум очень трудоемко, т.е. не очень эффективно. А если еще представить, что нам требуется формировать такие списки дат постоянно и динамически, т.е. начало и конец периода постоянно будут меняться, то такой ручной способ точно не подходит.

Поэтому сейчас мы рассмотрим способы для автоматической генерации последовательности дат.

Способы реализации генерации последовательности дат

В интернете можно встретить решения, которые подразумевают использование вспомогательных таблиц, однако в языке T-SQL все это можно сделать без каких-то внешних вспомогательных инструментов, т.е. с использованием только стандартных конструкций языка.

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

Сразу скажу, что оба способа по производительности примерно одинаковые и позволяют практически мгновенно сформировать последовательность дат за десятилетия и даже столетия.

Способ 1 – использование цикла WHILE

Первый способ подразумевает использование обычного цикла WHILE.

В отличие от ситуаций, когда нам требуется сформировать последовательность чисел или просто набор тестовых данных, эту тему мы рассматривали в отдельном материале – Как сформировать на языке T-SQL большое количество строк, в данном случае использовать цикл можно, так как даже если нам потребуется сформировать последовательности дат за несколько веков, у нас получится всего несколько десятков тысяч записей, которые сгенерируются достаточно быстро, тем более такое скорей всего будет требоваться только в каких-то частных случаях.

Итак, вот инструкция T-SQL, которая создает табличную функцию для генерации последовательности дат.

Принимает она два параметра: первый — начальная дата, и второй — дата окончания. В ответ она возвращает таблицу со всеми датами в этом промежутке.

   
   CREATE FUNCTION GeneratingDates (
	   @DateStart DATE, -- Дата начала
	   @DateEnd DATE	 -- Дата окончания
   )
   RETURNS @ListDates TABLE (dt DATE)
   AS
   BEGIN
	--Запускаем цикл. Он будет завершен, когда дойдем до даты окончания.
	WHILE @DateStart <= @DateEnd
	BEGIN
		--Добавляем запись со значением даты в результирующую таблицу.
		INSERT INTO @ListDates
			VALUES(@DateStart)
		
		SET @DateStart = DATEADD(DAY, 1, @DateStart) --Увеличиваем значение даты на 1 день
	END  
	
	RETURN
   END

Заметка! Список и расшифровка версий Microsoft SQL Server.

Способ 2 – использование рекурсивного обобщенного табличного выражения

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

Данная табличная функция работает точно так же как и предыдущая, и принимает ровно те же самые параметры.

   
   --Табличная функция для генерации последовательности дат (способ 2 – WITH)
   CREATE FUNCTION GeneratingDates (
	@DateStart DATE, -- Дата начала
	@DateEnd DATE	 -- Дата окончания
   )
   RETURNS @ListDates TABLE (dt DATE) 
   AS
   BEGIN

	--Рекурсивное обобщенное табличное выражение.
	WITH Dates AS
	(
		SELECT @DateStart AS DateStart -- Задаем якорь рекурсии
	
		UNION ALL

		SELECT DATEADD(DAY, 1, DateStart) AS DateStart -- Увеличиваем значение даты на 1 день
		FROM Dates
		WHERE DateStart < @DateEnd -- Прекращаем выполнение, когда дойдем до даты окончания
	)
	INSERT INTO @ListDates
		SELECT DateStart 
		FROM Dates
		OPTION (MAXRECURSION 0); 
		/*
			Значением 0 снимаем серверное ограничение на количество уровней рекурсии (которое по умолчанию равно 100), 
			чтобы иметь возможность формировать даты в большом диапазоне.
		*/
     RETURN
   END

Заметка! Как включить нумерацию строк кода в SQL Server Management Studio.

Пример использования функций для генерации последовательности дат

Теперь, когда у нас есть функция для генерации последовательности дат, давайте представим, что нам необходимо сформировать последовательность дат за 2020 год, т.е. нам нужны даты в промежутке начиная с 01.01.2020 и заканчивая 31.12.2020.

В итоге у нас должно быть 366 записей, т.е. отдельная запись для каждого дня года (в 2020 году 366 дней, так как это високосный год).

Таким образом, чтобы получить данную последовательность дат, мы обращаемся к нашей табличной функции и передаём в нее соответствующие значения (начало и конец года).

   
   SELECT * FROM GeneratingDates('01.01.2020','31.12.2020');

В результате мы получили то, что нам и было нужно.

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

Заметка! Курсы по T-SQL для начинающих.

На сегодня это все, надеюсь, материал был Вам полезен, пока!