Примеры getdate sql: GETDATE (Transact-SQL) — SQL Server

Как из даты получить год, месяц или день в T-SQL? Microsoft SQL Server | Info-Comp.ru

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

Содержание

1. Как в T-SQL получить текущую дату?
2. Пример – получение текущей даты в Microsoft SQL Server
3. Как получить год из даты в T-SQL?
4. Пример – получаем год из даты в Microsoft SQL Server
5. Как получить месяц из даты в T-SQL?
6. Пример – получаем месяц из даты в Microsoft SQL Server
7. Как из даты получить день в T-SQL?
8. Пример – получаем день из даты в Microsoft SQL Server
9. Как из даты получить час в T-SQL?
10. Пример – получаем час из даты в Microsoft SQL Server

Как в T-SQL получить текущую дату?

Для начала давайте я расскажу о том, как в Microsoft SQL Server можно получить значение текущей даты.

Для получения текущей даты в Microsoft SQL Server существует несколько специальных системных функций. Давайте некоторые из этих функций рассмотрим.

• GETDATE – функция возвращает значение, которое содержит дату и время компьютера, на котором запущен экземпляр Microsoft SQL Server, при этом смещение часового пояса не включается. Лично мне достаточно часто приходится пользоваться именно этой функцией;
• CURRENT_TIMESTAMP – эта функция эквивалентна функции GETDATE, она возвращает точно такое же значение. Вы можете использовать любую функцию, но как я уже сказал, лично я отдаю предпочтение функции GETDATE;
• SYSDATETIME – данная функция также возвращает дату и время компьютера, на котором запущен экземпляр Microsoft SQL Server, смещение часового пояса тоже не включается. Но в данном случае функция возвращает значение с более высокой точностью в долях секунды.

Примечание! Для того чтобы получить значение даты и времени с учетом смещения часового пояса, необходимо использовать функцию SYSDATETIMEOFFSET, а для того чтобы получить значение даты и времени в формате UTC функции GETUTCDATE или SYSUTCDATETIME.

Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.

Пример – получение текущей даты в Microsoft SQL Server

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

   
   SELECT GETDATE() AS [GETDATE],
           CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP],
           SYSDATETIME() AS [SYSDATETIME]

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

Как получить год из даты в T-SQL?

Если у Вас возникла необходимость из даты получить год, то есть, например, из 01.01.2019 получить 2019 в виде отдельного значения или просто из текущей даты получить год, то в Microsoft SQL Server Вы это можете сделать несколькими способами.

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

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

DATEPART принимает два параметра: первый, datepart, т.е. какую часть даты нам нужно вернуть, второй, дата, которую необходимо обработать.

Пример – получаем год из даты в Microsoft SQL Server

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

Чтобы DATEPART нам вернула год из даты, первым параметром нам необходимо передать значение, характеризующее часть «год», допустимо передавать следующие значения: year, yyyy или yy.

   
   --Объявляем переменную для хранения даты
   DECLARE @TestDate DATETIME
   --Присваиваем значение переменной (текущая дата)
   SET @TestDate = GETDATE()

   --Запрос SELECT
   SELECT @TestDate AS [Дата],
           
           --Передаем переменную в качестве параметра
           YEAR(@TestDate) AS [Год YEAR],
           DATEPART(YY, @TestDate) AS [Год DATEPART],
           
           --Передаем выражение, приводящее к типу DATE
           YEAR('01. 01.2019') AS [Год YEAR],
           
           --В качестве параметра указываем функцию
           DATEPART(YY, GETDATE()) AS [Год DATEPART]

Как получить месяц из даты в T-SQL?

В T-SQL из даты можно получить и номер месяца, для этого можно использовать функцию MONTH, она возвращает целое число, представляющее месяц указанной даты или все ту же функцию DATEPART, в которую, в данном случае необходимо будет передать значение, характеризующее часть даты «месяц», можно использовать: month, mm или m.

Пример – получаем месяц из даты в Microsoft SQL Server

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

   
   --Объявляем переменную для хранения даты
   DECLARE @TestDate DATETIME
   --Присваиваем значение переменной (текущая дата)
   SET @TestDate = GETDATE()

   --Запрос SELECT
   SELECT @TestDate AS [Дата],
           
           --Передаем переменную в качестве параметра
           MONTH(@TestDate) AS [Месяц MONTH],
           DATEPART(MM, @TestDate) AS [Месяц DATEPART],
           
           --Передаем выражение, приводящее к типу DATE
           MONTH('01. 01.2019') AS [Месяц MONTH],
          
           --В качестве параметра указываем функцию
           DATEPART(MM, GETDATE()) AS [Месяц DATEPART]

Как из даты получить день в T-SQL?

Для того чтобы получить из даты день, в T-SQL можно использовать функцию DAY – это функция возвращает целое число, представляющее день указанной даты. Также можно использовать и уже знакомую функцию DATEPART со значением первого параметра: day, dd или d.

Пример – получаем день из даты в Microsoft SQL Server

Здесь также мы используем несколько способов для получения дня из даты.

   
   --Объявляем переменную для хранения даты
   DECLARE @TestDate DATETIME
   --Присваиваем значение переменной (текущая дата)
   SET @TestDate = GETDATE()

   --Запрос SELECT
   SELECT @TestDate AS [Дата],
           
           --Передаем переменную в качестве параметра
           DAY(@TestDate) AS [День DAY],
           DATEPART(DD, @TestDate) AS [День DATEPART],
           
           --Передаем выражение, приводящее к типу DATE
           DAY('01. 01.2019') AS [День DAY],
          
           --В качестве параметра указываем функцию
           DATEPART(DD, GETDATE()) AS [День DATEPART]

Как из даты получить час в T-SQL?

Чтобы из даты получить час, мы можем использовать функцию DATEPART со значением hour или hh. Только в данном случае второй параметр (date), в котором мы передаем значение даты, должен обязательно содержать время, т.е. иметь тип данных DATETIME, тип DATE не допускается.

Пример – получаем час из даты в Microsoft SQL Server

В этом примере мы из даты получаем час.

   
   --Объявляем переменную для хранения даты
   DECLARE @TestDate DATETIME
   --Присваиваем значение переменной (текущая дата)
   SET @TestDate = GETDATE()

   --Запрос SELECT
   SELECT @TestDate AS [Дата],
           
           --Передаем переменную в качестве параметра
           DATEPART(HH, @TestDate) AS [Час],
          
           --В качестве параметра указываем функцию
           DATEPART(HH, GETDATE()) AS [Час]

У меня все, надеюсь, перечисленные выше примеры помогут Вам в решении Ваших задач. Начинающим программистам рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать на T-SQL в Microsoft SQL Server.

Пока!

текущая дата и другие функции даты в времени

Функция текущей даты SQL CURDATE() и её аналоги CURRENT_DATE() и CURRENT_DATE среди других функций даты и времени
применяются наиболее часто из-за широких возможностей, обеспечиваемых ими для анализа данных.
Знакомство с функциями даты и времени начнём с разбора практических примеров, демонстрирующих
возможности функции текущей даты. А затем перейдём к остальным функциям даты и времени, соблюдая для
удобства их классификацию по назначению.

Функция текущей даты CURDATE() возвращает
значение текущей даты в формате ‘YYYY-MM-DD’ и ‘YYYYDDMM’. Вычисляя несколькими способами (их как раз и
разберём в этом параграфе) разницу значений дат, можно определить такие важные значения, как возраст человека, его трудовой стаж,
продолжительность различных процессов и явлений и многое другое.

В примерах работаем с базой данных «Театр». Таблица Play содержит данные о постановках. Таблица Team —
о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные
и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

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

Пример 1. Сформировать список актеров старше 70 лет. Пишем следующий запрос:

SELECT
FName, LName, BirthDate
FROM ACTOR WHERE
TIMESTAMPDIFF(YEAR, BirthDate, CURDATE()) > 70

В этом запросе вычисляется разница между текущей датой CURDATE() и датой рождения
актёра BirthDate, содержащейся в таблице ACTOR. Для вычисления разницы применена функция TIMESTAMPDIFF().
Ключевое слово YEAR — задаёт единицу измерения — в годах интервала между датами. Вычисленное значение и
результат его сравнения с числом 70 вполне
пригодны в качестве условия выборки в секции WHERE. Следует учесть, что функция TIMESTAMPDIFF()
существует лишь в MySQL. В других диалектах SQL для этого есть функция DATEDIFF, а для задания
единицы измерения применяются различные ключевые слова в различных вариантах написания.

Для вычисления разницы дат можно использовать и оператор «минус». Это сделано в следующем примере.

Пример 2. Вывести список актеров, которые не задействованы в новых постановках
(в постановках последних 3 лет). Использовать CURDATE(), NOT IN. Запрос будет следующим:

SELECT fname, lname
FROM actor WHERE
actor_id NOT IN(SELECT actor_id FROM team
WHERE play_id IN(SELECT play_id FROM play
WHERE YEAR(premieredate) — YEAR(CURDATE())

В этом запросе задействована функция YEAR(). Аргументами её являются дата премьеры
постановки premieredate из таблицы play и текущая дата. Для вычисления разницы использован оператор
«минус». Он имеет больший приоритет по сравнению с оператором сравнения и поэтому выполняется первым.
Вычисленнное выражение сравнивается с числом 3 и подходящие значения служат условием выборки из таблицы
play, содержащей данные о постановках. Этот подзапрос — самый глубокий в «матрёшке» целого запроса.
Подзапрос более высокого уровня из таблицы team, содержащей данные о ролях, выбирает идентификаторы актёров,
с помощью предиката IN. Выбираются те актёры, которые участвовали в постановках трёх послежних лет. И,
наконец, самый внешний запрос к таблице actor выбирает значения с отрицанием (NOT IN) значения предыдущего
подзапроса.

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

Пример 3. Сформировать список актеров с их стажем (в днях).
Использовать CURDATE(), GROUP BY. Запрос будет следующим:

SELECT DISTINCT
a.Actor_ID, a.FName, a.LName,
CURDATE() — p1.PremiereDate AS ExpDays
FROM Play p1
JOIN
Team t1 ON p1.play_id = t1.play_id
JOIN
Actor a
ON t1.actor_id = a.Actor_id
WHERE t1.ACTOR_ID = a.Actor_ID
ORDER BY ExpDays, a.Actor_ID
DESC

В этом запросе разница между текущей датой CURDATE() и датой премьеры постановки
PremiereDate из таблицы Play вычисляется как имя столбца в результирующей таблице. Поскольку эти даты
имеют один и тот же формат, для вычисления разницы достаточно использовать оператор «минус». Разница
вычислена. Но из таблицы Play невозможно напрямую «достучаться» до таблицы Actor, содержащей данные об
актёрах. Поэтому используем соединение (JOIN) этой таблицы с таблицей Team, которая уже связана с таблицей
Actor при помощи ключа Actor_ID. Соединение таблиц Team и Actor — второе в этой цепочке из трёх таблиц.

Составить SQL запросы с текущей датой самостоятельно, а затем посмотреть решения

Пример 4. Определить самого востребованного актера за последние 5 лет.
Оператор JOIN использовать 2 раза. Использовать CURDATE(), LIMIT 1.

Правильное решение и комментарий.

Пример 5. Определить спектакли, в которых средний возраст актеров
от 20 до 30 (использовать BETWEEN, GROUP BY, AVG).

Правильное решение и комментарий.

В последующих параграфах приведено большинство функций даты и времени, используемых в СУБД MySQL.
А примеры использования наиболее часто применимых в MS SQL Server функций DATEDIFF и DATEADD
приведены соответственно на странице 2 и странице 3.

CURDATE(), CURRENT_DATE(), CURRENT_DATE — возвращают текущую дату в формате
‘YYYY-MM-DD’ или YYYYDDMM в зависимости от того, вызывается функция в текстовом или числовом контексте.

CURTIME(), CURRENT_TIME(), CURRENT_TIME — возвращают текущее время суток в формате
‘hh-mm-ss’ или hhmmss в зависимости от того, вызывается функция в текстовом или числовом контексте.

NOW() — возвращает текущие дату и время формате
‘YYYY-MM-DD hh:mm:ss’ или YYYYDDMMhhmmss в зависимости от того, вызывается функция в текстовом или числовом контексте.

TIMEDIFF(param1, param2) — возвращает разницу между
значениями времени, заданными параметрами param1 и param2.

DATEDIFF(param1, param2) — возвращает разницу между
датами param1 и param2. Значения param1 и param2 могут иметь типы DATE или DATETIME, а при
вычислении разницы используется лишь часть DATE.

PERIOD_DIFF(param1, param2) — возвращает разницу в месяцах между
датами param1 и param2. Значения param1 и param2 могут быть представлены в числовом формате
YYYYMM или YYMM.

TIMESTAMPDIFF(interval, param1, param2) — возвращает разницу между
значениями датами param1 и param2. Значения param1 и param2 могут быть представлены в форматах
‘YYYY-MM-DD’ или ‘YYYY-MM-DD hh:mm:ss’. Единица измерения разницы задаётся параметром interval. Он может
принимать значения FRAC_SECOND (микросекунды), SECOND (секунды), MINUTE (минуты), HOUR (часы),
DAY (дни), WEEK (недели), MONTH (месяцы), QUARTER (кварталы), YEAR (годы).

ADDDATE(date, INTERVAL value) — возвращает дату, к которой
прибавлено значение value. Ключевое слово INTERVAL обязательно следует в запросе, после него
указывается значение value, а затем единицы измерения прибавляемого значения. Ими могут быть
SECOND (секунды), MINUTE (минуты), HOUR (часы), MINUTE_SECOND (минуты и секунды),
HOUR_MINUTE (часы и минуты), DAY_SECOND (дни, часы минуты и секунды), DAY_MINUTE (дни, часы и минуты),
DAY_HOUR (дни и часы), YEAR_MONTH (годы и месяцы).

SUBDATE(date, INTERVAL value) — вычитает из величины даты date
произвольный временной интервал и возвращает результат. Ключевое слово INTERVAL обязательно следует в запросе, после него
указывается значение value, а затем единицы измерения вычитаемого значения. Возможные единицы измерения —
те же, что и для функции ADDDATE().

SUBTIME(datetime, time) — вычитает из величины времени datetime вида
‘YYYY-MM-DD hh:mm:ss’ произвольно заданное значение времени time и возвращает результат.

PERIOD_ADD(period, N) — добавляет N месяцев к значению
даты period. Значение period должно быть представлено в числовом формате ‘YYYYMM’ или ‘YYMM’.

TIMESTAMPADD(interval, param1, param2) — прибавляет к дате и времени
суток param2 в полном или кратком формате временной интервал param1, единицы измерения которого заданы
параметром interval. Возможные единицы измерения — те же, что и для функции TIMESTAMPDIFF().

DATE(datetime) — извлекает из значения даты и времени суток
в формате DATETIME (‘YYYY-MM-DD hh:mm:ss’) только дату, отсекая часы, минуты и секунды.

TIME(datetime) — извлекает из значения даты и времени суток
в формате DATETIME (‘YYYY-MM-DD hh:mm:ss’) только время суток, отсекая дату.

TIMESTAMP(param) — принимает в качестве аргумента дату и время
суток в полном или кратком формате и возвращает полный вариант в формате
DATETIME (‘YYYY-MM-DD hh:mm:ss’).

DAY(date), DAYOFMONTH(date) — принимают в качестве аргумента дату,
и возвращают порядковый номер дня в месяце (от 1 до 31).

DAYNAME(date) — принимает в качестве аргумента дату,
и возвращает день недели в виде полного слова на английском языке.

DAYOFWEEK(date) — принимает в качестве аргумента дату,
и возвращает порядкоый номер дня недели от 1 (воскресенье) до 7 (суббота).

WEEKDAY(date) — принимает в качестве аргумента дату,
и возвращает порядкоый номер дня недели от 0 (понедельник) до 6 (воскресенье).

WEEK(date) — принимает в качестве аргумента дату,
и возвращает номер недели в году для этой даты от 0 до 53.

WEEKOFYEAR(datetime) — возвращает порядковый номер недели в году
для даты datetime от 1 до 53.

MONTH(datetime) — возвращает числовое значение месяца года от 1 до 12
для даты datetime.

MONTHNAME(datetime) — возвращает строку с названием месяца
для даты datetime.

QUARTER(datetime) — возвращает значение квартала от 1 до 4
для даты datetime, которая может быть передана в формате ‘YYYY-MM-DD’ или ‘YYYY-MM-DD hh:mm:ss’.

YEAR(datetime) — возвращает год от 1000 до 9999
для даты datetime.

DAYOFYEAR(date) — возвращает порядковый номер дня в году
от 1 до 366 для даты date.

HOUR(datetime) — возвращает значение часа от 0 до 23 для
времени datetime.

MINUTE(datetime) — возвращает значение минут от 0 до 59 для
времени datetime.

SECOND(time) — возвращает количество секунд для времени суток
time, которое задаётся либо в виде строки ‘hh:mm:ss’, либо числа hhmmss.

EXTRACT(type FROM datetime) — принимает дату и время суток datetime
и возвращает часть, определяемую параметром type. Значениями параметра могут быть YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND.

TO_DAYS(date) — принимает дату date в кратком ‘YYYY-MM-DD’ или
полном формате ‘YYYY-MM-DD hh:mm:ss’ и возвращает количество дней, прошедших с нулевого года.

FROM_DAYS(N) — принимает количество дней N, прошедших
с нулевого года, и возвращает дату в формате ‘YYYY-MM-DD’.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(datetime) — если параметр не
указан, то возвращает количество секунд, прошедших с 00:00 1 января 1970 года. Если параметр datetime
указан (в кратком ‘YYYY-MM-DD’ или полном формате ‘YYYY-MM-DD hh:mm:ss’), то возвращает разницу
в секундах между 00:00 1 января 1970 года и датой datetime.

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp, format) —
принимает количество секунд, прошедших с 00:00 1 января 1970 года и возвращает дату и время суток
в виде строки ‘YYYY-MM-DD hh:mm:ss’ или в виде числа YYYYDDMMhhmmss в зависимости от того, вызвана
функция в строковом или числовом контексте.

TIME_TO_SEC(time) — принимает время суток time в формате
‘hh:mm:ss’ и возвращает количество секунд, прошедших с начала суток.

SEC_TO_TIME(seconds) — принимает количество секунд seconds,
прошедших с начала суток и возвращает время в формате ‘hh:mm:ss’ или hhmmss в зависимости от того, вызвана
функция в строковом или числовом контексте.

MAKEDATE(year, dayofyear) — принимает год year, номер дня в году
dayofyear и возвращает дату в формате ‘YYYY-MM-DD’.

MAKETIME(hour, minute, second) — принимает часы hour, минуты
minute и секунды second и возвращает время суток в формате ‘hh:mm:ss’.

• Функция текущей даты SQL, её возможности
• Функции, возвращающие текущие дату, время, дату и время
• Функции для вычисления разницы между моментами
• Функции для добавления некоторого значения к моменту
• Функции, характеризующие момент (значение аргумента)
• Функции для преобразования разницы в дни и секунды
• Примеры с функцией DATEDIFF MS SQL Server
• Примеры с функцией DATEADD MS SQL Server

Поделиться с друзьями

sql server — SQL, GETDATE, где пункт

спросил
3 года 11 месяцев назад

Изменено
3 года, 11 месяцев назад

Просмотрено
14 тысяч раз

Я пытаюсь использовать свой запрос как текущий день для моего SQL-запроса.

То, что я пытаюсь сделать ниже, возвращает 0 записей:

 выбрать *
из [ТЕСТ].[dbo].LIMIT
где endOfDay = GETDATE()
выбирать *
из [ТЕСТ].[dbo].LIMIT
где endOfDay = dateadd(dd, datediff(dd, 0, getdate()), 0)
где endOfDay >= getdate() и endOfDay < getdate() + 1
 

Примером даты будет 2019-07-09 00:00:00.0

Кроме того, если сегодняшняя дата не указана, могу ли я получить диапазон дат?

Будем признательны за любую помощь, спасибо.

• sql
• sql-сервер

3

Во-первых, вам нужно опустить кавычки, иначе 'GETDATE()' будет просто строкой.

Во-вторых, чтобы сопоставить «день», вам нужно удалить часть времени из результата GETDATE() , что вы можете сделать, используя вместо этого CAST(GETDATE() as DATE) :

 select *
из [ТЕСТ].[dbo].LIMIT
где endOfDay = CAST(GETDATE() как DATE)
 

Чтобы это работало лучше всего, столбец endOfDay также должен иметь тип 9. 0017 ДАТА . Если это что-то другое, вам нужно также CAST или CONVERT endOfDay в DATE .

0

Попробуйте это:

 выберите *
из [ТЕСТ].[dbo].LIMIT
где cast(endOfDay как дата) = cast(GETDATE() как дата)
 

SQL Fiddle , чтобы узнать, с чем будет сравниваться дата.

4

Использование кавычек приводит к поиску бессмысленного литерала 'GETDATE()' ничего не найти

Вы можете использовать

 с LIMIT(сегодня, EndOfDay) как
(
выберите CONVERT (DATETIME, CONVERT (дата, GETDATE ())), GETDATE ()
)
выберите конец дня
  от ПРЕДЕЛА
 где EndOfDay >= сегодня и EndOfDay < сегодня + 1;
 

Демо

8

GETDATE() возвращает текущую дату сервера и время . Если у вас нет записей, точно соответствующих этому, нет смысла использовать 9.0017 ПОЛУЧИТЬДАТА . Это не похоже на то, что вы делаете, исходя из имени столбца endOfDay .

Другие критерии, которые вы показываете,
dateadd(dd,datediff(dd,0,getdate()),0) по существу удаляет время и возвращает полночь текущей даты.

 выберите getdate() 2019-07-11 15:10:09.287
выберите датудобавить (дд, датифф (дд, 0, получитьдату ()), 0) 2019-07-11 00:00:00.000
 

5

Предполагая, что вас не волнует время, преобразуйте EndofDay и Getdate в простую дату для сравнения.

 выберите * из [ТЕСТ].[dbo].LIMIT, где convert(date,EndOfDay) = convert(date,getdate())
 

1

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

Функции даты SFMC SQL | Mateusz Dąbrowski

Управляйте датами с помощью встроенных функций SQL. Пусть запрос сделает свою работу.

Реализация Salesforce Marketing Cloud SQL не поддерживает определяемые пользователем функции. Однако существует несколько встроенных функций, полезных в повседневной работе с запросами.

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

GETDATE & GETUTCDATE

Функция GETDATE — самый простой способ получить текущую метку времени в запросе.

 ВЫБЕРИТЕ GETDATE() AS CurrentDate 
 

Вы должны знать

GETDATE() получает серверное время. Для Salesforce Marketing Cloud это означает центральное стандартное время (UTC-6) без изменений между стандартным и летним временем. Его нельзя изменить, даже с настройками часового пояса и культуры в программе «Настройка».

Вы можете получить дату и время с соответствующей информацией о смещении, используя функцию SYSDATETIMEOFFSET() . Он вернет ту же дату и время, что и GETDATE() , но в формате, демонстрирующем смещение часового пояса: 2020-12-28 15:08:01.7239173 -06:00 .

Вы можете увидеть 24-часовой формат часов и миллисекунды и смещение UTC-6 в конце. Эта последняя информация будет полезна для преобразования часовых поясов с помощью функции AT TIME ZONE .

Вы также можете использовать `TODATETIMEOFFSET' для работы со стандартными ПОЛУЧИТЬДАТА .

Функция GETUTCDATE работает так же, как GETDATE , но возвращает дату и время в часовом поясе UTC.

 SELECT GETUTCDATE() AS CurrentUTCDate 
 

Оба возвращают объект даты и времени (например, 30 октября 2020 1:15 утра ), поэтому для использования других функций даты в результате этой функции преобразование не требуется.

Однако вы можете ограничить его вывод датой или временем, используя ПРЕОБРАЗОВАТЬ или ФОРМАТ Функции.

DATEPART​

Функция DATEPART возвращает выбранную часть предоставленной даты.

Найти всех подписчиков, присоединившихся в 2020 году

 SELECT 
 SubscriberKey 
 , EmailAddress 
 FROM Ent._Subscribers 
 WHERE DATEPART(YEAR, DateJoined) = 2020 
 

It выводит целое число, поэтому убедитесь, что вы используете число при сравнении . Если вам нужен строковый вывод, проверьте аналогичный DATENAME 9Функция 0018.

Вы должны знать

Для наиболее популярных вариантов использования также есть три сочетания клавиш: ГОД() , МЕСЯЦ() и ДЕНЬ() .

Найти всех подписчиков, присоединившихся в 2020 г. — более короткий подход бэкэнд, эти функции являются просто обертками вокруг DATEPART , так что вы можете использовать все, что вы предпочитаете.

Части даты​

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

90 232

9001 7 DAYOFYEAR , DAY и WEEKDAY звучат похоже друг на друга, но возвращают немного другое значение. DAYOFYEAR сообщит вам, какой сегодня день в году (например, 257 ). ДЕНЬ возвращает номер дня в месяце (например, 14 ). WEEKDAY возвращает номер дня недели (например, 2 для понедельника).

Для 99% потребностей SFMC вам подойдут ГОД , МЕСЯЦ , ДЕНЬ , ЧАС и МИНУТА . Использование только тех, у кого есть полное имя, сделает ваши запросы читабельными и понятными для всех.

DATENAME​

Функции DATENAME возвращают строковое значение указанной части даты из заданной даты.

Захват названия всего дня

 SELECT 
 SubscriberKey 
 , DATENAME(WEEKDAY, DateJoined) AS DayJoined 
 FROM Ent._Subscribers 
 

Это может помочь быстро сократить дату до важной части (например, года для юбилейной персонализации или захват юбилейных закромов). Тем не менее, его выдающаяся функция возвращает название дня для заданной даты, благодаря ДЕНЬ НЕД часть даты.

Создайте автоматизацию, которая проведет встречи в выходные дни для пятничного напоминания

 SELECT ADPCRIBERKEY 
 от продавцов 
, где DateName (Weekday, DayOfmeeting) в («Пятница», «Суббота», «воскресенье») 
 (День («Пятница». ), DayOfMeeting) < 3 
 

Работает и для месяцев: DATENAME(MONTH, GETDATE()) .

Поскольку DATENAME возвращает строку, вы не сможете произвести какие-либо вычисления с результатом, даже если он будет содержать только цифры (например, DATENAME из части даты YEAR ). Если вы хотите выполнять операции, вы должны использовать функцию DATEPART .

DATEFROMPARTS & DATETIMEFROMPARTS​

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

DATEFROMPARTS позволяет создать значение даты из трех целых чисел, представляющих год, месяц и день.

 SELECT 
 SubscriberKey 
 , DATEFROMPARTS(2022, 01, 31) AS DateValue 
 

DATETIMEFROMEPARTS — родственная функция, которая требует больше данных, но выводит полноценное значение даты и времени. К году, месяцу и дню добавьте час, минуту, секунду и миллисекунду. Вы не сможете увидеть временную часть в пользовательском интерфейсе Salesforce Marketing Cloud (Contact Builder или Email Studio), но она будет доступна для расчетов.

 ВЫБЕРИТЕ 
 SubscriberKey 
 , DATETIMEFROMPARTS(2022, 01, 31, 23, 59, 59, 0) AS DatetimeValue 
 

Существует также третья функция: TIMEFROMEPARTS . В качестве аргументов требуются час, минута, секунды, дробь и точность. Его полезность в Marketing Cloud близка к нулю.

Вы должны знать

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

Однако имейте в виду, что если хотя бы одна предоставленная часть даты является NULL , весь вывод будет NULL .

DATEADD​

Функции DATEADD дают вам возможность складывать и вычитать значения даты.

Добавить семь дней к текущей дате UTC

 SELECT 
 GETUTCDATE() AS TrialStartDate 
 , DATEADD(DAY, 7, GETUTCDATE()) AS TrialEndDate 
 

Требуется три вещи:

1. Часть даты — элемент даты что будет изменено
2. Число — целое число (или выражение, вычисляемое как целое число) для интервала
3. Дата — исходная дата, которую функция DATEADD изменит. Это может быть любой тип данных даты. Вывод будет того же типа.

Вы должны знать

DATEADD не учитывает смещение часового пояса.

Однако, если вы измените дату с 31-го числа месяца на несколько месяцев и итоговый месяц станет короче, SQL учтет это и вернет последний день данного месяца.

DATEADD особенно полезен при использовании с оператором WHERE , так как он позволяет динамически фильтровать записи:

Найти всех подписчиков, присоединившихся в течение последнего месяца 1 ОТ Ent. _Subscribers
WHERE DateJoined >= DATEADD(MONTH, -1, GETDATE())

Еще лучше с диапазонами:

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

 SELECT 
 SubscriberKey 
 , EmailAddress 
 FROM EventRegistration 
 WHERE EventDate МЕЖДУ GETDATE() И DATEADD(MONTH, 1, GETDATE()) 
 

Вы должны знать

Аналогичный расчет даты также доступен с 9 0017 ДАТАДОБАВИТЬ() Функция AMPScript. В зависимости от варианта использования подумайте, хотите ли вы манипулировать датой в расширении данных или только в сообщении.

Если поле даты может быть пустым, обязательно используйте COALESCE или добавьте условие проверки NULL .

DATEDIFF​

Функция DATEDIFF вычисляет разницу между двумя датами.

Необходимо указать часть даты, используемую для расчета, и две даты (начальный и конечный порядок).

Вы можете использовать его в операторе SELECT :

Проверить, как долго кто-то был подписчиком

 SELECT 
 SubscriberKey 
 , DATEDIFF(DAY, DateJoined, GETDATE()) AS SubscriptionTime 
 FROM Ent. _Subscribers 
 

Вы можете использовать его в операторе WHERE :

Получить всех, кто достиг одного года в списке подписки

 SELECT SubscriberKey 
 FROM Ent._Subscribers 
 WHERE DATEDI FF(DAY, DateJoined, GETDATE( )) = 365 
 

Вы должны знать

DATEDIFF может выводить положительные и отрицательные числа в зависимости от предоставленных значений и их порядка.

 SELECT 
 DATEDIFF(DAY, DATEADD(DAY, -1, GETDATE()), GETDATE()) AS YesterdayPostive /* Вывод: 1 */ 
 , DATEDIFF(DAY, GETDATE(), DATEADD(DAY, -1, GETDATE())) AS YesterdayNegative /* Вывод: -1 */ 
 

Это очень важно для корректного сравнения внутри WHERE .

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

Вы также можете использовать DATEDIFF для захвата диапазонов.

Найти всех подписчиков, зарегистрированных на мероприятие в течение следующих 31 дня

 SELECT 
 SubscriberKey 
 , EmailAddress 
 FROM EventRegistration 
 WHERE DATEDIFF(DAY, GETDATE(), EventDate) <= 31 
 

Это короче, чем DATEADD , но с другим уровнем контроля . Поскольку DATEDIFF работает с частями даты, будет учитываться только выбранная часть. Например:

Разные диапазоны, одинаковый результат

 SELECT 
 DATEDIFF(MONTH, '31 марта 2021', '01 апреля 2021') AS ShortDiff /* Вывод: 1 */ 
 , DATEDIFF(MONTH, '01 марта 2021' , '30 апреля 2021') AS LongDiff /* Вывод: 1 */ 
 

Несмотря на то, что даты в первом DATEDIFF разделены всего на день, а во втором почти на два месяца, оба возвращают 1 . Причина в том, что DATEDIFF сравнивает только часть даты месяца — март и апрель — это разница в один месяц.

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

Вы должны знать

Существует предел тому, насколько большой может быть разница. Если вы спуститесь на SECOND часть даты, максимальная разница во времени запроса составляет 68 лет, 19 дней, 3 часа, 14 минут и 7 секунд. Это не должно быть проблемой в Marketing Cloud для большинства случаев использования.

Если поле даты может быть пустым, обязательно используйте COALESCE или добавьте условие проверки NULL .

В ЧАСОВОМ ПОЯСЕ

Функция В ЧАСОВОМ ПОЯСЕ предоставляет две невероятные возможности:

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

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

Вы должны знать

AT TIME ZONE всегда выводит тип данных DATETIMEOFFSET . Если вам нужен другой тип/формат, используйте CONVERT или FORMAT .

Добавить смещение с помощью AT TIME ZONE​

Когда вы работаете с часовыми поясами, вы хотите иметь смещение для используемых полей даты и времени. К сожалению, в большинстве случаев он отсутствует. Ни временные метки системы Salesforce Marketing Cloud, ни популярные Функция GETDATE предоставляет информацию о смещении.

Вы можете быстро исправить это с помощью В ЧАСОВОМ ПОЯСЕ . Когда вы применяете эту функцию к дате и времени без часового пояса, предполагается, что вы определяете ее и возвращаете значение с соответствующим смещением. 18 00:00 -06:00 */
, CONVERT(DATETIME2(0), '2020-07-01T18:00:00') AT TIME ZONE 'Central Standard Time' AS CDT /* Вывод: 2020-07-01 18 :00:00 -05:00 */

В приведенном выше фрагменте мы ПРЕОБРАЗОВАЕМ даты в дату и время без смещения. Затем функция AT TIME ZONE добавляет смещение и вычисляет для вас изменение смещения летнего времени.

Вы должны знать

Не во всех доступных часовых поясах соблюдается летнее время. Например, Central Standard Time соответствует летнему времени, а Central America Standard Time — нет. Всегда дважды проверяйте поведение часового пояса, который вы выбираете для своего запроса.

AT TIME ZONE vs TODATETIMEOFFSET​

Каким бы удивительным это ни казалось, учет перехода на летнее время является проблемой при работе с системными датами Salesforce Marketing Cloud (включая GETDATE ).

Salesforce Marketing Cloud использует центральное стандартное время (UTC-6) без изменений между стандартным и летним временем. Это означает, что отличная функция В ЧАСОВОМ ПОЯСЕ работает против нас.

Однако есть два обходных пути:

1. Стандартное время Центральной Америки . Это часовой пояс, подобный CST, который остается с одним и тем же смещением в течение всего года.

Без волшебства перехода на летнее время

 ВЫБЕРИТЕ 
 ПРЕОБРАЗОВАТЬ (DATETIME2 (0), '2020-01-01T18:00:00') AT TIME ZONE 'Стандартное время Центральной Америки' AS CST /* Вывод: 2020-01- 01 18:00:00 -06:00 */ 
 , CONVERT(DATETIME2(0), '2020-07-01T18:00:00') AT TIME ZONE 'Стандартное время Центральной Америки' AS AlsoCST /* Вывод: 2020- 07-01 18:00:00 -06:00 */ 
 

2. TODATETIMEOFFSET . Это упрощенная версия AT TIME ZONE , которая добавляет произвольное смещение без какой-либо логики при переходе на летнее время. Так же, как мы хотели бы для системных дат SFMC.

Чтобы использовать его, укажите дату и время и смещение (например, '-06:00' или '+01:00' ):

Без магии летнего времени

 SELECT 
 TODATETIMEOFF УСТАНОВИТЬ(ПРЕОБРАЗОВАТЬ (DATETIME2(0), '2020-01-01T18:00:00'), '-06:00') AS CST /* Вывод: 2020-01-01 18:00:00 -06:00 */ 
, TODATETIMEOFFSET(CONVERT(DATETIME2(0), '2020-07-01T18:00:00'), '-06:00') AS AlsoCST /* Вывод: 2020-07-01 18:00:00 -06: 00 */ 
 

Конечно, для любых значений даты и времени, включающих летнее время, загруженных с веб-сайта или из вашего хранилища данных, В ЧАСОВОМ ПОЯСЕ будет лучшим решением.

Изменение часового пояса с помощью AT TIME ZONE

AT TIME ZONE также полезно для изменения часового пояса даты и времени со смещением.

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

Изменить дату и время CST со смещением на CET

 SELECT CONVERT(DATETIMEOFFSET(0), '2018-01-01 18:00:00 -06:00') AT TIME ZONE 'Центральноевропейское стандартное время' AS CSTtoCET /* Вывод : 2020-01-02 01:00:00 +01:00 */ 
 

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

Вы должны знать

Вы можете использовать обе функции В ЧАСОВОМ ПОЯСЕ в одной строке, чтобы применить часовой пояс к дате и времени без смещения и преобразовать его в другой часовой пояс, используя функцию дважды:

Сначала примените часовой пояс. Затем измените часовой пояс

 SELECT CONVERT(DATETIME2(0), '2020-01-01T18:00:00') AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'Central European Standard Time' AS DateToCSTtoCET, /* Вывод: 2020-01-02 01:00:00 +01:00 */ 
 

Это работает, потому что при первом выполнении функции добавляется смещение с AT TIME ZONE . Второй использует вывод с примененным смещением и изменяет часовой пояс.

Конечно, для системных дат Salesforce Marketing Cloud нам по-прежнему нужен один из обходных путей:

Без магии перехода на летнее время, но с изменением часового пояса

 SELECT 
 GETDATE() AT TIME ZONE 'Стандартное время Центральной Америки' AT TIME ZONE 'Стандартное время Центральной Европы' AS SystemDateToCSTtoCET_v1 
 , TODATETIMEOFFSET(GETDATE(), '-06 :00') AT TIME ZONE 'Центральноевропейское стандартное время' AS SystemDateToCSTtoCET_v2 
 

Доступные часовые пояса​

Вы можете выбрать один из 140 доступных часовых поясов:
Стандартное время линии дат , UTC-11 , 9, UTC-08 , Тихоокеанское стандартное время , Стандартное горное время США , Стандартное горное время (Мексика) , Стандартное горное время , Стандартное время Центральной Америки , Стандартное центральное время , Стандартное время острова Пасхи , Центральное стандартное время (Мексика) , Центральное стандартное время Канады , Южно-тихоокеанское стандартное время , Восточное стандартное время (Мексика) , Восточное стандартное время , Стандартное время Гаити , Стандартное время Кубы , Стандартное восточное время США , Стандартное время Парагвая , Стандартное атлантическое время , Стандартное время Венесуэлы , Стандартное время Центральной Бразилии , Стандартное время Южной Австралии , Стандартное время Тихоокеанского региона Южной Америки , Стандартное время Теркс и Кайкос , Стандартное время Ньюфаундленда , Стандартное время Токантинса , Стандартное время Восточной Америки , Стандартное время Восточной Америки 90 018 , Стандартное время Аргентины , Стандартное время Гренландии , Стандартное время Монтевидео , Стандартное время Магалланес , Стандартное время Сен-Пьер , Стандартное время Баии , UTC-02 , Среднеатлантическое стандартное время , Стандартное время Азорских островов , Стандартное время Кабо-Верде , UTC , Стандартное время Марокко , Стандартное время по Гринвичу , 9001 7 Стандартное время по Гринвичу , Западная Европа Стандартное время Время , Стандартное время Центральной Европы , Стандартное время Романс , Стандартное время Центральной Европы , Стандартное время Западной Африки , Стандартное время Намибии , Стандартное время Иордании , Стандартное время GTB , Стандартное время Ближнего Востока , Стандартное время Египта , Стандартное время Восточной Европы , Стандартное время Сирии , Стандартное время Западного берега , Стандарт Южной Африки Время , Стандартное время Франции , Стандартное время Израиля , Стандартное время Калининграда , Стандартное время Ливии , Стандартное арабское время , Стандартное время Турции , Стандартное арабское время , Стандартное время Беларуси , Стандартное время России , Стандартное время Восточной Африки , Стандартное время Ирана , Стандартное арабское время , Стандартное время Астрахани Время , Азербайджан, стандартное время , Часовой пояс России 3 , Стандартное время Маврикия , Стандартное время Саратова , Стандартное время Грузии , Стандартное время Кавказа , Стандартное время Афганистана , Стандартное время Западной Азии , Стандартное время Екатеринбурга , Стандартное время Пакистана , Стандартное время Индии , Стандартное время Шри-Ланки , Стандартное время Непала , 9001 7 Стандартное время Центральной Азии , Стандартное время Бангладеш Время , Стандартное время Омска , Стандартное время Мьянмы , Стандартное время Юго-Восточной Азии , Стандартное время Алтая , Стандартное время Западной Монголии , Стандартное время Северной Азии , Стандартное время Северной Азии , Стандартное время Томска , Стандартное время Китая , Стандартное время Северной Азии , Стандартное время Сингапура , Стандартное время Западной Австралии 90 018 , Стандартное время Тайбэя , Стандартное время Улан-Батора , Стандартное время Северной Кореи , Центральный Запад Австралии. Стандартное время , Стандартное время Забайкалья , Стандартное время Токио , Стандартное время Кореи , Стандартное время Якутска , Центр. Стандартное время Австралии , Стандартное время Центральной Австралии , Стандартное время Восточной Австралии , Стандартное восточное время Австралии , Стандартное время западной части Тихого океана , Стандартное время Тасмании , Стандартное время Владивостока , Стандарт лорда Хоу Время , Стандартное время Бугенвиля , Часовой пояс России 10 , Стандартное время Магадана , Стандартное время Норфолка , Стандартное время Сахалина , Стандартное время Центральной части Тихого океана , Часовой пояс России 11 , Стандартное время Новой Зеландии , UTC+12 , Стандартное время Фиджи , Стандартное время Камчатки Время , Стандартное время островов Чатем , UTC+13 , Стандартное время Тонга , Стандартное время Самоа , Стандартное время островов Лайн .

Не беспокойтесь о названиях для перехода на летнее время - В ЧАСОВОМ ПОЯСЕ используйте стандартные и позаботятся о переходе на летнее время.

ФОРМАТ с датами

ФОРМАТ работает только с датой и временем и числовыми значениями, выводит строку и требует:

1. Форматируемое значение (может быть столбцом, строкой или функцией) применяется
2. Опционально - код культуры ISO

Это идеальный инструмент для управления любыми датами (или строками, подобными дате, с помощью преобразования), доступными в ваших расширениях данных. Для персонализации. Для сравнения между системными и импортированными датами вне запроса. Для подготовки данных для экспорта во внешнюю систему с особыми требованиями к формату.

Вместо создания сложной логики AMPScript для адаптации дат отформатируйте их

 SELECT 
 FORMAT(CONVERT(DATE, '30/10/2020'), 'd', 'en-GB') AS DateOrderedProperly 
 , FORMAT(GETUTCDATE (), 'дд/мм/гггг') AS TodayCustomFormat 
 , FORMAT(DateJoined, 'D', 'de-DE') AS DateExpandedIndian 
 

В приведенном выше примере первые две функции FORMAT возвращают дату в тот же формат - 30/10/2020 - один с использованием значения, предоставленного вручную, другой с использованием текущей даты UTC из функции.

В последней строке выводится Freitag, 7. Februar 2020 . Удобно запускать его с CASE , чтобы изменить вашу короткую дату на расширенную дату, персонализированную на языке пользователя.

Стандартные форматы даты

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

Спецификатор формата	Описание	Пример (для культуры США)
'd'	Короткая дата	30.10.2020
'D'	Длинная дата 902 42	Пятница, 30 октября 2020 г.
'м'/'М'	Месяц День	30 октября
'г'/'Г'	Год Месяц	Октябрь 2020
'т'	Короткое время	14:15
'T '	Давно	14:15:30
'g'	Общий кратковременный	30. 10.2020 14:15
'G'	Общий длительный	30.10.2020 14:15:30
'f'	Полный короткий рабочий день	Пятница, 30 октября 2020 г. 14:15
'F'	Полный длительный срок	Пятница, 3 октября 0, 2020 14:15:30
'r'/'R'	RFC1123	Пт, 30 октября 2020 г. 14:15:30 GMT

Как выделено в заголовке таблицы, примеры относятся к форматированию культуры США по умолчанию SFMC. Если вы добавите определенный код культуры в качестве третьего параметра, результат будет другим, поскольку в разных странах используются разные значения по умолчанию для порядка элементов и стиля разделителя.

Полный список описателей стандартного формата даты можно найти на страницах документации Microsoft .Net.

Пользовательские форматы даты

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

9 0241 Сокращенный месяц

902 41 2020

Опять же, примеры относятся к форматированию культуры США по умолчанию и могут выглядеть по-разному для разных культур (например, разделитель часов может автоматически измениться на .