Self-Learning – видеокурсы и книги по IT

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

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

Генерация последовательности дат в заданном диапазоне на T-SQL

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

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

dt
01.01.2020
02.01.2020
03.01.2020
04.01.2020
05.01.2020
06.01.2020
07.01.2020
08.01.2020
09.01.2020
10.01.2020
11.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

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

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

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

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

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

Скриншот 1

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

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

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

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

Мы в соцсетях
Следите за выходом новых статей в наших группах в социальных сетях: Подписывайтесь, и Вы не пропустите выход нового материала!
Теги: #
Понравилась статья? Поделитесь с друзьями:
Share on VK
VK
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on Tumblr
Tumblr
Share on Reddit
Reddit
Email this to someone
email

Авторские онлайн-курсы по T-SQL

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

  1. Аватар
    Александр

    Здравствуйте, Виталий! Хотелось бы увидеть статьи по управлению производительностью сервера БД и мониторингу планов запросов.

    1. Self-Learning
      Self-Learning

      Здравствуйте, Александр
      Хорошее предложение!
      Возможно, в будущем подготовлю соответствующие материалы.
      Спасибо!

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Пролистать наверх