Иногда нам приходится использовать T-SQL для вычисления времени, прошедшего между двумя событиями, значения даты и времени которых получены в качестве входных данных. Для некоторых целей достаточно вычислить разницу как длительность какой-нибудь части, такой как секунды, с использованием функции DATEDIFF или DATEDIFF_BIG. Первая возвращает разницу в желаемой части в виде четырехбайтового целого числа (INT); вторая (добавленная в SQL Server 2016) возвращает разницу как 8-байтовое целое число (BIGINT). Возникает сложность, особенно в версиях, предшествующих SQL Server 2016, когда разница не укладывается в возвращаемый тип, но ее не очень трудно устранить. Значительно сложнее вычислить разницу как комбинацию частей, начиная с годов и вплоть до наносекунд. Например, если мы имеем два входных значения даты и времени @t1 = '19710212 12:00:00.0000001' и @dt2 = '20170208 12:00:00.0000000', результатом вычислений должна стать разница 45 лет, 11 месяцев, 26 дней, 23 часа, 59 минут, 59 секунд и 999 999 900 наносекунд, с положительным знаком (так как @dt1 < @dt2).

В этой статье будет показано решение задачи в виде встроенной функции, возвращающей табличное значение (TVF) с именем DATEDIFFPARTS. Она принимает два входных значения DATETIME2, именуемых @dt1 и @dt2, и возвращает набор результатов с одной строкой, с одним столбцом для знака результата (1, когда @dt1 < @dt2, -1, когда @dt1 > @ft2, 0, когда они одинаковы, и NULL, если какое-нибудь значение равно NULL) и одним столбцом для каждой из частей.

Это довольно интересная задача, поэтому я рекомендую вам попытаться решить ее самостоятельно, прежде чем посмотреть мое решение. Используйте программный код для тестирования с двумя постоянными входными значениями, как на рисунке 1.

 

Входные значения
Рисунок 1. Входные значения

Предполагается, что выходные данные должны быть как на рисунке 2.

 

Выходные значения
Рисунок 2. Выходные значения

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

Предполагается, что этот программный код выдаст выходные данные (разделенные на две части для ясности), как на рисунке 3. Желаю вам успеха!

 

Выходные значения функции
Рисунок 3. Выходные значения функции

Решение

В листинге 2 дано определение подготовленной мною функции DATEDIFFPARTS.

Я использую последовательность операторов CROSS APPLY, чтобы сделать элементы, созданные одним оператором, доступными последующим операторам, следуя семантике логической обработки запросов. Если вам необходимо освежить знания о том, как работает оператор APPLY, и об особенностях логической обработки запросов, нужную информацию можно найти в статье «Логическая обработка запросов: предложение FROM и оператор APPLY»...

Это не вся статья. Полная версия доступна только подписчикам журнала. Пожалуйста, авторизуйтесь либо оформите подписку.
Купить номер с этой статьей в PDF