Функции даты и времени SSIS

Функции даты и времени SSIS — это встроенные функции языка выражений служб SQL Server Integration Services (SSIS), предназначенные для извлечения, преобразования, формирования и сравнения значений даты и времени (типы DT_DATE, DT_DBTIMESTAMP, DT_DBTIME, DT_DBDATE и др.) в потоках данных, переменных и компонентах задачи «Выражение». Они позволяют выполнять операции над компонентами даты (год, месяц, день) и времени (часы, минуты, секунды, миллисекунды), а также генерировать текущие значения даты/времени.

Типы данных даты и времени в SSIS

SSIS использует собственные типы данных, отличные от типов SQL Server. Основные типы, поддерживаемые функциями даты и времени:

• DT_DATE — структура, состоящая из целого числа (количество дней с 1 января 1899 года) и дробной части (время суток). Диапазон: от 1 января 1899 года до 31 декабря 9999 года.
• DT_DBTIMESTAMP — структура с полями года, месяца, дня, часа, минуты, секунды и дробной части секунды (до 999 наносекунд). Диапазон: от 1 января 1753 года до 31 декабря 9999 года.
• DT_DBTIME — только время (часы, минуты, секунды, дробные секунды). Диапазон: от 00:00:00 до 23:59:59.999.
• DT_DBDATE — только дата (год, месяц, день). Диапазон: от 1 января 1753 года до 31 декабря 9999 года.
• DT_FILETIME — 64-битное целое число, представляющее количество 100-наносекундных интервалов с 1 января 1601 года.

При использовании функций необходимо учитывать совместимость типов: многие функции ожидают на входе DT_DATE или DT_DBTIMESTAMP, а при несоответствии типов требуется явное преобразование с помощью функций CAST или (DT_...).

Основные функции даты и времени

Функции получения текущей даты и времени

• GETDATE() — возвращает текущую дату и время в формате DT_DBTIMESTAMP. Значение фиксируется на момент начала выполнения пакета или компонента.
• GETUTCDATE() — возвращает текущую дату и время по Гринвичу (UTC) в формате DT_DBTIMESTAMP.
• CURRENT_TIMESTAMP — синоним GETDATE(), возвращает текущую дату и время в формате DT_DBTIMESTAMP.

Функции извлечения компонентов даты и времени

• DATEADD(«часть», число, дата) — добавляет указанное число единиц к заданной части даты. Часть задаётся строковым параметром: «yy» (год), «mm» (месяц), «dd» (день), «hh» (час), «mi» (минута), «ss» (секунда), «ms» (миллисекунда). Возвращает DT_DBTIMESTAMP.
• DATEDIFF(«часть», начальная_дата, конечная_дата) — возвращает целое число — разницу между двумя датами в указанных единицах.
• DATEPART(«часть», дата) — возвращает целое число, представляющее указанную часть даты (год, месяц, день, час, минута, секунда, миллисекунда).
• DAY(дата) — возвращает день месяца (1–31) как целое число.
• MONTH(дата) — возвращает месяц (1–12) как целое число.
• YEAR(дата) — возвращает год как целое число.

Функции формирования даты и времени

• DATENAME(«часть», дата) — возвращает строковое представление указанной части даты (например, месяц — «January», день недели — «Monday»). Работает только с DT_DATE и DT_DBTIMESTAMP.
• MAKEDATE(год, месяц, день) — создаёт значение DT_DBDATE из трёх целочисленных аргументов. Доступна в SSIS 2012 и новее.
• MAKETIME(час, минута, секунда) — создаёт значение DT_DBTIME из трёх целочисленных аргументов. Доступна в SSIS 2012 и новее.

Функции проверки и преобразования

• ISNULL(выражение) — проверяет, является ли выражение NULL. Часто используется для проверки дат на NULL перед выполнением операций.
• ISDATE(выражение) — возвращает логическое значение TRUE, если выражение является допустимой датой (DT_DATE или DT_DBTIMESTAMP), иначе FALSE. Работает со строковыми аргументами.

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

Вычисление возраста на основе даты рождения

`` DATEDIFF("yy", [BirthDate], GETDATE()) - CASE WHEN DATEADD("yy", DATEDIFF("yy", [BirthDate], GETDATE()), [BirthDate]) > GETDATE() THEN 1 ELSE 0 END ``

Формирование даты начала текущего месяца

`` MAKEDATE(YEAR(GETDATE()), MONTH(GETDATE()), 1) ``

Преобразование строки в дату

`` (DT_DBTIMESTAMP)"2023-12-25 10:30:00" ``

Проверка, является ли дата выходным днём

`` DATEPART("dw", [OrderDate]) IN (1,7) ``

Особенности и ограничения

• Все функции даты и времени чувствительны к регистру (например, DATEADD и dateadd — разные идентификаторы). В SSIS все ключевые слова пишутся прописными буквами.
• Функции не поддерживают работу с часовыми поясами напрямую. Для перевода времени в другой часовой пояс приходится использовать арифметические операции (например, DATEADD("hh", 3, [UTCDate]) для перехода с UTC на московское время).
• При работе с DT_DATE и DT_DBTIMESTAMP возможна потеря точности: DT_DATE хранит время с точностью до 1/86400 секунды (около 11,6 микросекунд), а DT_DBTIMESTAMP — до 1/1000 секунды (1 миллисекунда).
• Значения NULL в датах приводят к ошибкам выполнения. Перед использованием функций рекомендуется проверять на NULL с помощью ISNULL или обрабатывать в компоненте «Условное разбиение».
• В ранних версиях SSIS (2005, 2008) отсутствовали функции MAKEDATE и MAKETIME — для формирования даты приходилось использовать конкатенацию строк и преобразование типов.

Применение

Функции даты и времени широко используются в следующих сценариях:

• Вычисляемые столбцы в компоненте «Производный столбец» (Derived Column) — для расчёта возраста, длительности, периода.
• Переменные пакета — для задания временных меток (например, имя файла с датой: "Report_" + (DT_WSTR, 4)YEAR(GETDATE()) + "_" + (DT_WSTR, 2)MONTH(GETDATE()) + ".csv").
• Условные выражения в компоненте «Условное разбиение» (Conditional Split) — для фильтрации строк по дате (например, «только заказы за последние 7 дней»).
• Задачи «Выполнение SQL» — для формирования параметров запросов с датами.

Сравнение с функциями T-SQL

Функции SSIS во многом аналогичны функциям T-SQL, но имеют отличия:

• В T-SQL используется GETDATE() без скобок, в SSIS — со скобками.
• В T-SQL DATEPART и DATEDIFF принимают сокращения частей без кавычек (например, year), а в SSIS — в кавычках ("yy").
• В SSIS нет функции EOMONTH (конец месяца) — её приходится эмулировать через DATEADD и MAKEDATE.
• В SSIS нет функции FORMAT — форматирование даты выполняется через преобразование в строку и конкатенацию.

Источники

1. Microsoft Docs. «Integration Services (SSIS) Expressions». Раздел «Date and Time Functions».
2. Microsoft Docs. «SSIS Data Types: Date and Time».
3. Книга: «Microsoft SQL Server 2012 Integration Services» (авторы: Brian Knight, Erik Veerman, Jessica M. Moss).
4. Статья: «SSIS Date and Time Functions: A Complete Guide» (SQL Shack, 2020).