Содержание

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



Я только что начал работать с SQL; мне нужно представить некоторую информацию в некоторых таблицах, дата и время-это две из них. Я хочу знать, есть ли лучший тип данных для представления даты и времени, чем varchar() или numeric(). Существуют ли какие-либо примитивы SQL для представления даты и времени?

sql
Поделиться Источник Cláudio Ribeiro     13 ноября 2011 в 16:06

2 ответа


  • SQLite тип данных даты и времени

    Я пытаюсь создать красивую небольшую базу данных для запуска в мобильном приложении (Windows Mobile 5, Если вам интересно). В документации SQLite тип данных даты и времени определяется следующим образом: 1.2 тип данных даты и времени SQLite не имеет класса хранения, выделенного для хранения дат и…

  • Объединить столбец даты и времени символа DataType В Тип данных DateTime

    Как только я выполню запрос, как мне снова собрать их вместе? Мне удалось выполнить следующий запрос, чтобы преобразовать значение даты в datetime и добавить к нему часть времени declare @date char(8), @time char(8) select @date=’20101001′,@time =’12:10:47′ select cast(@date as datetime)[email protected] В…



1

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

Точный синтаксис зависит от поставщика RDMS, но вот несколько примеров для очень популярных Microsoft SQL Server и Oracle (ранее Sun) MySQL .

MySQL поставляется со следующими типами данных для хранения даты или значения даты/времени в базе данных:

  • DATE-формат YYYY-MM-DD
  • DATETIME-формат: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP-формат: YYYY-MM-DD HH:MM:SS
  • YEAR-формат YYYY или YY

SQL Server поставляется со следующими типами данных для хранения даты или значения даты/времени в базе данных:

  • DATE-формат YYYY-MM-DD
  • DATETIME-формат: YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME-формат: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP-формат: уникальный номер

Или вы можете искать другие реализации баз данных. Экс:

«Oracle date time data type sql»

Вы можете найти:

Поделиться JohnB     13 ноября 2011 в 16:15



0

Это зависит от того, с каким вкусом вы работаете, но большинство вариаций SQL предлагают дату, smalldate, datetime, datetimeoffset и т. д.

На самом деле все зависит от того, как вы хотите показать дату и сколько информации вы хотите показать.

Наиболее распространенной формой является datetime, и она объясняется (наряду со всеми другими) в MSDN Transact-SQL

Поделиться Phoenix     13 ноября 2011 в 16:09


Похожие вопросы:


T-SQL: преобразование столбца даты varchar в тип даты

У меня есть следующий столбец date в базе данных SQL Server. В настоящее время тип данных varchar , а даты хранятся в следующем формате: 2010-04-24 Я выполняю следующую команду: ALTER TABLE games…


измените строку даты и времени на тип данных Sql server DateTime

как изменить эти строки даты и времени на тип sql server DateTime: Thu May 07 19:19:27 Thu May 07 19:19:33 Thu May 07 19:19:34 Thu May 07 19:19:34 Thu May 07 19:19:35


Какой тип данных наиболее подходит для хранения времени и даты

У меня есть два варианта хранения даты и времени в моей базе данных. Сгенерируйте дату time & из функции time в php и затем сохраните ее в базе данных в тип данных int, который имеет размер 4…


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

Я пытаюсь создать красивую небольшую базу данных для запуска в мобильном приложении (Windows Mobile 5, Если вам интересно). В документации SQLite тип данных даты и времени определяется следующим…


Объединить столбец даты и времени символа DataType В Тип данных DateTime

Как только я выполню запрос, как мне снова собрать их вместе? Мне удалось выполнить следующий запрос, чтобы преобразовать значение даты в datetime и добавить к нему часть времени declare @date…


Лучший способ хранения данных даты и времени в SQL Server 2008 R2 и SQL Server Compact 4

Мы разрабатываем приложение в C# 4, которое использует SQL Server 2008 R2 в качестве бэкенда. SQL Server Compact 4 также используется для отключенных клиентов в очень редких случаях. Мы задаемся…


Sql server 2005 тип данных для даты

Я хочу создать таблицу со столбцом DATE . Для этого столбца я использую тип данных DateTime . Есть ли какой-либо другой тип данных или опция для отображения только даты без времени? я использую Sql…


Получение Текущего Времени/Даты — Сохранение В Базе Данных SQL

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


Как эмулировать тип данных MySQL timestamp в SQL Server

MySQL имеет тип данных под названием timestamp. Это поле даты и времени, которое обновляется до now() при изменении любых данных в записи. Как я могу добиться такого же поведения в SQL Server? Я…


Как преобразовать поле даты в формат даты и времени? SQL 2008

Преобразование типа данных date в тип данных datetime привело к out-of-range значение. Заявление было прекращено. Мне просто нужно преобразовать поле даты в формат даты и времени.

Как объединить дату и время с datetime2 в SQL Server?

Это, кажется, работает и сохраняет точность:

SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))

CASTК DATETIME2(7)Преобразует TIME(7)значение ( @T) к DATETIME2где дата часть '1900-01-01', которая является значением по умолчанию даты и DateTime типа (см datetime2и комментарий * на CASTиCONVERT страницы в MSDN) .

* … Когда символьные данные, представляющие только компоненты даты или только времени, приводятся к типам данных datetime или smalldatetime, для неопределенного компонента времени устанавливается значение 00: 00: 00.000, а

для неопределенного компонента даты устанавливается значение 1900-01- 01 .

Функция DATEADD()и DATEDIFF()заботится об остальном, т.е. добавляет разницу в днях между 1900-01-01и DATEзначением ( @D).

Тест в: SQL-Fiddle


Как заметил @Quandary , вышеприведенное выражение считается недетерминированным в SQL Server. Если нам нужно детерминированное выражение, скажем, потому что оно должно использоваться для PERSISTEDстолбца, '19000101'** необходимо заменить на 0или CONVERT(DATE, '19000101', 112):

CREATE TABLE date_time
( d DATE NOT NULL,
  t TIME(7) NOT NULL,
  dt AS DATEADD(day, 
                DATEDIFF(day, CONVERT(DATE, '19000101', 112), d), 
                CAST(t AS DATETIME2(7))
               ) PERSISTED
) ;

**: DATEDIFF(day, '19000101', d)не является детерминированным, поскольку неявное преобразование строки в DATETIMEи преобразование из строки в дату и время являются детерминированными только при использовании определенных стилей.

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

Замечание 1

Существующие типы данных даты и времени YEAR, TIME, TIMESTAMP, DATE и DATETIME имеют собственный интервал допустимых значений, среди которых и значение «ноль», использующееся при введении пользователем действительно недопустимого значения.

Заметим, что MySQL может хранить некоторые не совсем достоверные значения даты, к примеру, 1999-11-31. Причиной тому является то, что управлять проверкой даты должно конкретное приложение, а не SQL-сервер. Чтобы ускорить проверку правильности даты, MySQL проверяет попадание месяца в интервал 0–12 и дня в интервал 0–31. Эти интервалы начинаются с 0 с той целью, чтобы MySQL мог хранить в столбцах DATETIME или DATE даты с днем и месяцем равным 0. Такой вариант полезен, например, для приложений, предполагающих хранение даты рождения, когда не всегда известен месяц или день рождения. Тогда хранение даты происходит в виде 1999-00-00 или 1999-01-00 (для таких дат функции DATE_ADD или DATE_SUB() могут дать неправильные значения).

MySQL интерпретирует значения в нескольких форматах, но всегда ожидается, что даты задаются в порядке год-месяц-день (к примеру, ’99-08-05′). Значение, которое имеет тип даты или времени, автоматически преобразуется MySQL в число, когда данную величину используют в виде числа, и наоборот.

Значение, которое имеет тип даты или времени и выходит за границы указанного интервала или недопустимо для данного типа данных, MySQL преобразует в значение «ноль». Исключением являются величины типа TIME, которые выходят за границы установленного интервала и усекаются до граничной точки заданного интервала TIME.

В таблице рассмотрены форматы значения «ноль» для каждого типа столбцов:

Проблема 2000 года для типов данных

MySQL устойчив к «проблеме 2000 года», но некоторые входные величины могут стать источниками ошибок. Например, если ввести двухразрядное значение года, то оно будет интерпретироваться неоднозначно, т.к. не указано столетие. Такие значения должны переводиться в четырехразрядную форму, поскольку MySQL для представления года использует 4 разряда.

Даты с неоднозначным годом в MySQL для типов YEAR, TIMESTAMP, DATE и DATETIME интерпретируются согласно правилам:

  • значение года от 00 до 69 конвертируется в 2000–2069;
  • значение года от 70 до 99 конвертируется в 1970–1999.

Тип TIMESTAMP

Тип TIMESTAMP обеспечивает автоматическую запись текущей даты и времени при использовании операции INSERT или UPDATE. Если используется несколько столбцов типа TIMESTAMP, то автоматически обновляется лишь первый из них.

Тип DATE

Тип DATE содержит величины с информацией о дате в формате ‘YYYY-MM-DD’. Для данного типа год может изменяться в пределах диапазона 1000–9999, а значения месяца и числа в пределах года. Т.е. данные обрабатываются в диапазоне ‘1000-01-01’–’9999-12-31’.

Тип DATETIME

Тип DATETIME используется для величин, которые содержат значения даты и времени. MySQL обрабатывает значения в формате ‘YYYY-MM-DD HH:MM:SS’, которые соответствуют диапазону ‘1000-01-01 00:00:00’–’9999-12-31 23:59:59’.

Тип TIME

MySQL обрабатывает значения данного типа в формате ‘HH:MM:SS’. Для больших значений часа (при указании временного интервала) используется формат ‘HHH:MM:SS’. Значения TIME должны попадать в диапазон от ‘-838:59:59’ до ‘838:59:59’.

Тип YEAR

Тип данных YEAR является однобайтным и содержит значение года.

MySQL обрабатывает значения в формате YYYY и диапазоне от 1901 до 2155.

Недопустимые значения YEAR преобразуются в 0000.

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

Аргументы функций даты и времени имеют тип DATE. За исключением функции        которая возвращает значение типа NUMBER, все функции возвращают значения типа DATE. В этом разделе рассматриваются также арифметические операции над датами. Функции даты и времени описываются в следующей таблице.

ADD_MONTHS(d, х)

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

d, то возвращается последний день месяца-результата. Если число дней не меньше, то день месяца-результата и день месяца d совпадают. Временные компоненты даты d и результата одинаковы.

CURRENTDATE        

Возвращает текущую дату во временной зоне сеанса как значение типа DATE. Эта функция аналогична SYSDATT за исключением того, что SYSDATE не зависит от временной зоны текущего сеанса.

CURRENTJIMESTAMP

Возвращает текущую дату во временной зоне сеанса как значение WITH TIMEZ0NE.

Если задан параметр precision (точность), он представляет десятичную точность возвращаемого числа секунд; по умолчанию используется 6.

DBTIMEZONE

Возвращает временную зону базы данных. Используется такой же формат, как и в операторе CREATE DATABASE или в самом последнем операторе ALTER DATABASE.

EXTRACT({YEAR. MONTH, DAY,

HOUR, MINUTE, SECOND,  

TIMEZONE HOUR,

TIMEZONE_MINUTE,

TIMEZONE REGION,

TIMEZONE_ABBR} FROM

datetime_or_interval)

Возвращает выбранные данные из параметра datetime_or_interval, который должен быть выра­жением типа DATE, DATETIME или INTERVAL Запрашиваемое поле должно быть допустимым -например, нельзя извлечь TIMEZONE из значения DATE. Если поле недопустимо, Oracle* будет порождать ошибку «ORA-30076: invalid extract field for extract source».

LAST_DAY(d)

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

LOCALTIMESTAMP

Возвращает текущую дату во временной зоне сеанса как значение типа TIMESTAMP. Если определен параметр precision, он представляет десятичную точность числа возвращаемых секунд; по умолчанию используется 6. LOCALTIMESTAMP возвращает значение TIMESTAMP, в то время как CURRENTJIMESTAMP возвращает значение TIMESTAMP WITH TIMEZONE.

MONTHS_BETWEEN

Возвращает число месяцев между датой и датой date2. Если дни в date2 совпада­ют или если обе даты являются последними днями своих месяцев, то результат представляет собой целое число. В противном случае результат будет содержать дробную часть (в отноше­нии к 31-дневному месяцу).

NEW_TIME

Возвращает дату и время часового пояса zone2 для даты и времени часового пояса zonel, заданных значением d. Zone 1 и zone2 — это строки символов, их возможные значения приведены в таблице 5.1. В Oracle можно определить дополнительные имена временных зон с помощью представления v$timezone_names.

NEXT_DAY

Возвращает в string дату первого дня, наступающего после даты d. Строка string указывает день недели на языке текущего сеанса. Временной компонент возвращаемого значения тот же, что и временной компонент d. Регистр символов строки значения не имеет.

ROUND

Округляет дату d до единицы измерения, указанной в format. Форматы, применяемые в функциях ROUND и TRUNC, описаны в таблице 5.2. Если формат не указан, принимается формат по умолчанию ‘DD’, который округляет d до ближайшего дня.

SESSIONTIMEZONE

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

SYS_EXTRACT_UTC(

Возвращает время в UTC (Универсальное координированное время, ранее — Среднее время по Гринвичу) из предоставленного параметра datetime, который должен включать в себя часовой пояс.

SYSDATE

Возвращает текущие дату и время в формате типа DATE. Аргументов нет. При использовании в распределенных SQL-операторах функция SYSDATE возвращает дату и время локальной базы данных.

SYSTIMESTAMP

Возвращает текущие дату и время в формате типа TIMESTAMP WITH TIMEZONE.

При использовании в распределенных SQL-операторах SYSTIMESTAMP возвращает дату

и время локальной базы данных.

TRUNC

Возвращает дату d, усеченную до единицы измерения, указанной с помощью format. Применяются те же форматы, что и в функции ROUND (см. таблицу 5.2). Если formaf опущен, принимается формат по умолчанию ‘DD’, который усекает d до ближайшего дня.

TZ_OFFSET

Возвращает в виде строки символов смещение между заданным часовым поясом timezone и UTC. Аргумент timezone можно определить либо как название часового пояса (см. таблицу 5.1 или представление v$timezone_names), либо как смещение в формате +/-НН:МГ. Также могут использоваться функции SESSIONTIMEZONE и DBTIMEZONE. 

Арифметические операции с датами и временем

Выполнение арифметических операций над датами (d). датами и време­нем (dt), интервалами (г) и числами (га) описывается в таблице 5.3. Обра­тите внимание, что при вычитании одной даты из другой результатом является число.

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

< Предыдущая   Следующая >

sql — Как преобразовать дату и время (SQL Server) в дату и время Excel?

Проблема . Я хочу преобразовать datetime SQL Server в формат даты и времени Excel.

Пример 1: от 2018-08-23 15:32:32.000 до 43335,65

Пример 2: от 1985-03-26 10:35:42.000 до 31132,44

Что я пробовал: вдохновленный этим ответом, я попробовал этот запрос

SELECT DATEDIFF(day, @MyDatetime, GETDATE())

Который работает, если вы хотите конвертировать дату (без часов), но как конвертировать время тоже?

В Excel время сохраняется в десятичной части числа, поэтому я попытался использовать

SELECT DATEDIFF(SECOND, '1899/12/30 00:00:00.000', GETDATE())

Но это приводит к ошибке переполнения.

Функция datediff привела к переполнению. Число частей даты, разделяющих два экземпляра даты / времени, слишком велико. Попробуйте использовать datediff с менее точной частью даты.

1

Nicolaesse 23 Мар 2018 в 17:59

3 ответа

Лучший ответ

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

select CAST(GETDATE() as float)+2

Как даты работают в Excel: https://www.excelcampus.com/functions/how-dates -Работа -в- Excel /

В принципе, любая дата может быть сохранена как количество дней с 01.01.1900. И время является дробным значением, которое равно (number_of_seconds_since_midnight) * (1 / (24 * 60 * 60))

Нужно добавить 2 дня, как Excel и SQL подсчитывают количество дней с другой даты начала.

Надеюсь, что все это имеет смысл.

3

GSazheniuk 23 Мар 2018 в 15:11

Преобразуйте ваш DATETIME в FLOAT и добавьте 2

Пример

Declare @D datetime = '2018-08-23 15:32:32.000'

Select cast(@D as float)+2

Возврат

43335.6475925926

4

John Cappelletti 23 Мар 2018 в 15:08

Я просто получил дату из базы данных и позволил Excel преобразовать ее. Таким образом, если Excel когда-либо изменит свое внутреннее представление, ваш код SQL не изменится.

0

user1443098 23 Мар 2018 в 15:02

49452639

ITband.ru » Окончательный справочник по типам данных datetime

Цель этой статьи заключается в разъяснении особенностей работы с типами DATETIME в SQL Server, в том числе часто встречающихся заблуждений и общих рекомендаций по их преодолению. Благодаря Frank Kalis эта статья переведена на немецкий язык.

Благодарности:

Я хочу поблагодарить следующих людей за их ценные предложения и материалы для этой статьи: Steve Kass, Aaron Bertrand, Jacco Schalkwijk, Klaus Oberdalhoff, Hugo Kornelis, Dan Guzman и Erland Sommarskog.

Версии SQL Server

Данная статья применима к SQL Server 7.0, 2000, 2005 и 2008, если не указано иначе.

Типы даты и времени в SQL Server

До SQL Server 2008 в SQL Server имелось два типа данных для обработки даты и времени. Поскольку в этой статье мы очень часто будем ссылаться на эти типы, введем сокращенную аббревиатуру для каждого типа в двух представленных ниже таблицах (колонка СК):

Название

СК

Мин. значение

Макс. значение

Точность

Используемая память

smalldatetime sdt 1900-01-01 00:00:00 2079-06-06 23:59:00 минута 4 байта
datetime dt 1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 3.33 мс 8 байт

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

Если задавать только ту часть что касается даты, то SQL Server сохранит время в виде 00:00:00.000.
А если устанавливать значение только времени, SQL Server будет хранить дату как 01.01.1900.
Это очень важно. Прочтите снова.

SELECT CAST(‘20041223’ AS datetime)

———————–
2004-12-23 00:00:00.000

SELECT CAST(’14:23:58′ AS datetime)

———————–
1900-01-01 14:23:58.000

С появлением SQL Server 2008 было получено несколько новых типов данных связанных со значениями даты и времени:

Название

СК

Мин. значение

Макс. значение

Точность

Используемая память

datetime2 dt2 0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999 100 нс 6-8 байт
date d 0001-01-01 9999-12-31 день 3 байта
time t 00:00:00.0000000 23:59:59.9999999 100 нс 3-5 байта
datetimeoffset dto 0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999 100 нс 8-10 байт
  • Как вы можете видеть, мы наконец-то получили типы данных только для даты (date) и только для времени (time).
  • Datetime2 это “лучшее DATETIME” по ряду причин, и занимает не многим больше памяти, чем datetime и потенциально даже меньше!
  • Для нового типа включающего величину времени, вы можете задать “точность до дробных секунд” определяя используемый порядок цифр в секундах после запятой. Так, time(3) может хранить значения наподобие 14:23:12.567, которые при вводе значения 14:23:12:5677 округляются до 14:23:12:568.
  • Новый тип datetimeoffset содержит в себе часть смещения местного часового пояса.

Форматы даты и времени

Распространенное заблуждение – то, что SQL Server хранит эти типы данных в некотором специфическом удобном для чтения формате. Это не так. SQL Server хранит такие значения в своем внутреннем формате (например, два целых числа для datetime и smalldatetime). При этом, когда используется T-SQL для установки значения (например, в выражении INSERT) вы выражаете его как текстовую строку. Существуют также правила для интерпретации SQL Server-ом различных форматов даты-строки. Но заметим, что в любом случае SQL Server не запомнит этот формат.

Входные форматы для даты и времени

Есть много доступных форматов для приведения значений к виду date/time/datetime. Некоторые из них “лучше”, чем другие, и далее вы поймете почему “лучше”. Примечательно, что все эти форматы применимы для всех типов. Так даже формат “только время” применимо для типа “только дата” и т.д . (В статье игнорируется часть смещения местного часового пояса, которая используется только в типе данных datetimeoffset – более подробно о котором в Books Online.)

Название

СК

Формат

SET DATEFORMAT зависимость

SET LANGUAGE зависимость

Нейтральность к языку

Unseparated u ‘19980223 14:23:05’ нет нет для всех
Separated s ’02/23/1998 14:23:05′ для всех для всех нет
ANSI SQL ansisql ‘1998-12-23 14:23:05’ sdt, dt sdt, dt не для sdt и dt
Alphabetic a ’23 February 1998 14:23:05′ нет для всех (название месяца) нет
ODBC datetime odt {ts ‘1998-02-23 14:23:05’} нет нет для всех
ODBC date od {d ‘1998-02-23’} нет нет для всех
ODBC time ot {t ’14:23:05′} нет нет для всех
ISO 8601 iso ‘1998-02-23T14:23:05’ нет нет для всех
Time t ’14:23:05′
‘2:23:05 PM’
нет нет для всех
  • Отметим, ANSI SQL действительно только частный случай формата с разделительными символами Separated (так называемый “цифровой”), использующий в качестве разделителей тире (-), косую черту (/) и точку (.). Но, поскольку это единственный формат, определенный в стандарте ANSI SQL, по мнению автора это стоит упомянуть как частный случай.
  • Большинство форматов позволяют удалять часть содержащую дату и/или время, и в некоторых случаях это может выглядеть немного … странно. Казалось бы, неразумно указывать, например ‘2008-08-25’ как тип время (time), но в конечном результате это аналогично не установке значений в строке datetime. Рассмотрим ниже:
    SELECT CAST(” AS time)
    SELECT CAST(‘2008-08-25’ AS time)

    Оба запроса выдают один и тот же результат (time 00:00:00).
  • ODBC форматы (ODBC datetime, ODBC date, ODBC time) отличаются в том смысле, что у них есть маркер (literal_type, t, d или ts), который необходимо правильно задать в зависимости от того, получать ли дату и время, только дату или только время.
  • Для применения формата ISO 8601 необходим сегмент даты и времени.
  • SET DATEFORMAT наследует свои настройки от SET LANGUAGE (но явно заданный SET DATEFORMAT аннулирует более поздний SET LANGUAGE). Языковые настройки по умолчанию задаются для каждого языка используемого при вводе логина. Язык по умолчанию для логина задается при помощи sp_configure.
  • Правила, относительно форматирования части даты и новых типов могут привести к путанице. Microsoft стремится, чтобы дата новых соответствующих типов данных (date, datetime2 и datetimeoffset) была менее зависима от настроек и еще более соответствовала требованиям ANSI SQL. И в результате – новые типы нейтрально-языковые для выделения составляющих даты-времени до тех пор пока год на первом месте. SQL Server-у необходимо определить, что эта часть является годом, и поэтому требуется 4 позиции составляющих год (yyyy, а не yy). Если это так, то строка будет интерпретироваться как сначала год, затем месяц и, наконец, день – независимо от DATEFORMAT или языковых установок. Если же вначале указывается месяц, тогда DATEFORMAT и языковые настройки будут “соблюдаться”:
    SET LANGUAGE British –uses dmy
    GO
    SELECT CAST(’02-23-1998 14:23:05′ AS date) –Error
    GO
    SELECT CAST(‘2/23/1998 14:23:05’ AS date) –Error
    GO
    SELECT CAST(‘1998-02-23 14:23:05’ AS date) –Ok
    GO
    SELECT CAST(‘1998.02.23 14:23:05’ AS date) –Ok
    GO
    SELECT CAST(‘1998/02/23 14:23:05’ AS date) –Ok
    GO
    Первые два запроса ошибочны поскольку год не на первой позиции (и нет 23 месяца в 1998 году). В последующих трех запросах ошибок нет, поскольку требования учтены, и год указан первым (и мы используем один из новых стилей типов связанных с датой). Предельно прозрачно, правда? 🙂

Описание доступных форматов имеется в Books Online, поэтому вдаваться в подробности для каждого формата нет смысла.

Здесь приведена ссылка на Books Online, смотри подраздел для каждого формата. Отметим, что только формат ODBC time выдаст текущую дату, а не 1900-01-01 как в других форматах типа “только время”.

Некоторые выводы:

  • Формат Alphabetic использовать не рекомендуется, поскольку SET LANGUAGE влияет на все типы.
  • Не рекомендуется использовать типы smalldatetime и datetime поскольку они зависят не только от SET LANGUAGE, но и от SET DATEFORMAT (в том числе языковые параметры логирования).
  • Вы думаете, что не имеет значения, как комбинация формат/тип влияет (или нет) на SET LANGUAGE, “Я все равно никогда не буду выполнять команду SET LANGUAGE”. Но обратите внимание, предельно осторожно, что значения по умолчанию для DATEFORMAT подтягивается из спецификации логина (CREATE LOGIN т.д.). Представьте ситуацию, когда приложения разработанные в США с логином имеющим по-умолчанию язык(us_english), использующие m/d/y формат и datetime. К примеру, в Германии кто-то начнет использовать это приложение и бац! Приложение не работает. Удалось привлечь ваше внимание? 🙂
  • Типы представленные в SQL Server 2008 являются дружественными, поскольку они не зависят от SET DATEFORMAT, если у вас в Separated формате (с разделением) первым определен год. Для форматов с символами-разделителями (в том числе ANSI SQL), новые типы всегда интерпретируют месяц впереди даты.

Рекомендации для ввода

Как вы наверное понимаете, вы не хотите использовать сочетание формата и типа, не зависящих от языка, пока не убедитесь, что у вас есть правильный DATEFORMAT и языковые настройки в своих приложениях. Имейте в виду, что установка этих параметров в хранимых процедурах может привести к перекомпиляции процедуры во время ее выполнения!
Моя рекомендация заключается в использовании нейтрально-языкового формата. Я обычно использую формат без разделителей Unseparated, главным образом по старой привычке. Формат ISO 8601 имеет преимущество, поскольку определен в качестве международного стандарта.

SQL Server 2008 был выпущен в момент написания этой статьи, только я вернусь к date, time, datetime2 и возможно к datetimeoffset в следующий раз. В следующий раз, я мог бы использовать формат ANSI SQL (при совместимости ANSI SQL) с новыми типами, но автор предпочел бы, чтобы ANSI SQL поддерживал формат ISO 8601.

Безопасный путь это использовать или Unseparated, или ISO 8601 в течение ряда лет – даже если работаем с новыми типами данных.

Предупреждения и распространенные заблуждения

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

Числовой формат может использовать в качестве разделителя тире (-), точку (.) или косую черту (/).
Правила обработки SQL Server-ом строки, не меняются в зависимости от разделителя. Распространенное заблуждение то, что формат ANSI SQL (одно время неправильно называемый “ISO форматом”), 1998-02-23, нейтрально-языковой для smalldatetime и datetime. Это не так. Это цифровой формат и, следовательно, он зависит от установок SET DATEFORMAT и SET LANGUAGE:

SET LANGUAGE us_english
SELECT CAST(‘2003-02-28’ AS datetime)

———————–
2003-02-28 00:00:00.000

SET LANGUAGE british
SELECT CAST(‘2003-02-28’ AS datetime)

Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Пожалуйста, прочитайте внимательно сообщение об ошибке. Оно говорит о том, в чем конкретно проблема. Вы определяете значение datetime как строку, и потому что строка не сформирована согласно формату (который Вы используете) и настроек DATEFORMAT, SQL Server не может преобразовать строку в значение datetime.

Вывод значений DATETIME

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


SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)——–
20040312SELECT CONVERT(char(19), CURRENT_TIMESTAMP, 120)——————-
2004-03-12 18:08:43 SELECT CONVERT(char(10), CURRENT_TIMESTAMP, 103)———-
12/03/2004

Поиск для значений DATETIME

Проблема с datetime-поиском часто состоит в том факте, что если вы используете smalldatetime, datetime, datetime2 или datetimeoffset, которые состоят из таких двух частей как дата и время. Создадим таблицу, которую мы будем использовать в нескольких примерах:

CREATE TABLE #dts(c1 char(1), dt datetime)
INSERT INTO #dts (c1, dt) VALUES(‘a’, ‘20040305 09:12:59’)
INSERT INTO #dts (c1, dt) VALUES(‘b’, ‘20040305 16:03:12’)
INSERT INTO #dts (c1, dt) VALUES(‘c’, ‘20040306 00:00:00’)
INSERT INTO #dts (c1, dt) VALUES(‘d’, ‘20040306 02:41:32’)
INSERT INTO #dts (c1, dt) VALUES(‘e’, ‘20040315 11:45:17’)
INSERT INTO #dts (c1, dt) VALUES(‘f’, ‘20040412 09:12:59’)
INSERT INTO #dts (c1, dt) VALUES(‘g’, ‘20040523 11:43:25’)

Скажем так, мы желаем найти все строки с 2004-03-15. Распространенная ошибка при поиске выглядит так:

SELECT c1, dt FROM #dts WHERE dt = ‘20040305’

Нет результатов. Почему? Что случилось? У нас есть два различных типа в предложении WHERE, тип datetime (колонка dt) и строка (справа). SQL Server преобразует один к другому в соответствии с документацией “Приоритетность типов данных” в Books Online. Datetime имеет высокий приоритет, чем строка, так что строка будет первой преобразована в тип datetime. Поскольку мы не указываем часть со временем, строка будет преобразована к значению datetime 2004-03-05 00:00:00. А с таким значением нет ни одной записи.

Хорошо, следующая попытка. Мы используем функцию CONVERT для табличной колонки dt, и конвертируем ее в строку, чтобы она соответствовала формату строки указанной в правой части:
SELECT c1, dt FROM #dts WHERE CONVERT(char(8), dt, 112) = ‘20040305’

Этот запрос вернул ожидаемые строки, но есть одна проблема. Поскольку происходит вычисление колонки dt (с помощью функции CONVERT), SQL Server не может использовать индекс для поддержки этого поиска. Это может иметь катастрофические последствия по производительности! Давайте попробуем BETWEEN:
SELECT c1, dt FROM #dts WHERE dt BETWEEN ‘20040305’ AND ‘20040306’

Потому что запрос содержит BETWEEN, вернулись записи по 2004-03-06 00:00:00. Давайте попробуем следующее:
SELECT c1, dt FROM #dts WHERE dt BETWEEN ‘20040305’ AND ‘20040305 23:59:59.999’

Что? У нас по-прежнему есть запись по 2004-03-06 00:00:00. Почему? Мы должны посмотреть на определения типов снова. Точность для datetime-типа 3,33 мс. Это значит что 2004-03-05 23:59:59.999 будет округлена до 2004-03-06 00:00:00.000. Не то, что ожидалось. Для того чтобы обойти эту проблему округления, попробуем следующее:

SELECT c1, dt FROM #dts WHERE dt BETWEEN ‘20040305’ AND ‘20040305 23:59:59.997’

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

SELECT c1, dt FROM #dts WHERE dt BETWEEN ‘20040305’ AND ‘20040305 23:59:00’

Наличие двух различных решений в зависимости от используемых типов данных это не то что можно приветствовать. И, кто знает, что произойдет, если Microsoft в будущем добавит типы данных с высокой точностью для сегмента времени (что они и сделали в SQL Server 2008)? Снова вам придется корректировать долю времени в WHERE. Ниже рекомендации автора:

SELECT c1, dt FROM #dts WHERE dt >= ‘20040305’ AND dt < ‘20040306’

Мы получаем ожидаемые строки назад и легко понимаем за счет каких критериев. И SQL Server может использовать индекс для колонки dt. Для больших диапазонов этот поиск легко расширить. Скажем так, необходимо отобрать все строки для марта 2004 года:


SELECT c1, dt FROM #dts WHERE dt >= ‘20040301’ AND dt < ‘20040401’

Аналогично. Вот еще один способ для поиска всех строк за конкретный месяц:
SELECT c1, dt FROM #dts WHERE DATEPART(year, dt) = 2004 AND DATENAME(month, dt) = ‘March’

Выше возвращается ожидаемый результат, но остаются две проблемы. Поскольку в колонке с datetime есть расчеты, SQL Server в запросе не может использовать индекс для колонки dt. И, функция DATENAME зависит от установок SET LANGUAGE.

Избавляемся от временной части

Очень часто вам необходимо работать только с датой, и вам необходимо избавиться от временной части. Как вы знаете, сейчас это невозможно, если вы используете тип smalldatetime, datetime, datetime2 или datetimeoffset. Если вы используете тип date, тогда нет проблем – это та же дата, в конце концов! Но если вы настаиваете по какой-то причине использовать тип smalldatetime, datetime, datetime2 или datetimeoffset то, возможно, будет достаточно установки времени в 00:00:00? Вот мое предложение для выражения, возвращающего строку, которая может быть (неявно) преобразована в значение datetime, независимо от SET LANGUAGE или DATEFORMAT:

CONVERT(char(8), CURRENT_TIMESTAMP, 112)

Как видите, я использую CONVERT с конверсионным кодом 112, возвращающее дату в виде строки в формате Unseparated. А вы уже знаете, что формат Unseparated при конвертировании к datetime интерпретируется вне зависимости от даты или языковых настроек. Давайте вышеприведенное выражение добавим в CAST для преобразования значения к типу datetime:


SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS datetime)

В случае, если вы действительно хотите только вернуть в клиентское приложение дату, а не время, тогда вместо этого вам необходимо возвращать строку(или, как в SQL Server 2008, тип date). Еще раз прочтите о различных кодах преобразования для функции CONVERT и вы, вероятно, подберете необходимый Вам формат.

На эту тему Hugo Kornelis оставил свой отзыв, в котором предлагает иной метод для установки сегмента отвечающий за время в 00:00:00. Эта идея основана на базовой дате (любая дата находится в пределах диапазона datetime SQL Server-а), временной сегмент которой определен как 00:00:00.

Сначала берем разницу в днях между сегодняшним днем и этой базовой датой. Затем добавляем это количество дней к базовой дате. Алгоритм: [сегодня] – [базовая дата] + [базовая дата], или в T-SQL (с использованием в качестве базовой даты 20040101):
SELECT DATEADD(day, DATEDIFF(day, ‘20040101’, CURRENT_TIMESTAMP), ‘20040101’)

Одним из аргументов в пользу использования этого метода является более высокая скорость выполнения по сравнению со строковыми преобразованиями. Ранее автор считал что отличия настолько малы (несколько микросекунд в соответствии с тестами сделанными в 2000 году), так что они даже не измеримы. Однако Dan Guzman сообщил по электронной почте удивительную для меня информацию. Вот цитата из письма:

“Я обнаружил, что использование метода DATEADD значительно превосходит метод со строкой, когда выражение применяется к столбцу в больших запросах. Например, я часто использую метод DATEADD для группировок по дате, когда колонка datetime включает в себя компоненту время. Недавно я тестировал производительность, но я повторно вспоминаю, что в некоторых случаях это были минуты”.
Дэну я очень доверяю, и поэтому я думаю, что мне есть чему заново научиться (вначале используя метод DATEADD вместо метода со строкой). Дэн приводит скрипт демонстрирующий это.

Вот здесь (с некоторыми незначительными изменениями форматирования сделанными мною). Мои тесты показывают достоверные результаты на SQL Server 2005 и 2008, где для 10,000,000 строк метод DATEADD отрабатывает около 6 секунд, а строчный метод отрабатывал около 13 секунд. Дэн сообщает об еще большей разнице на SQL Server 2000.

2009-04-03, получено письмо от Håkan Borneland. Он отметил, что это обычное явление, для примера в форумах MSDN, используя первый из нижеприведенных вариантов, вместо второй альтернативы (которой автор изначально располагал в своем сценарии тестирования производительности):

SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, ”, CURRENT_TIMESTAMP))

Обратите внимание, что они оба отличаются в версии DATEDIFF, которую мы обсудили несколько выше. Так что теперь у нас есть 4 различных метода достижения этой цели. “Мой” оригинальный метод преобразования строк и три DATEDIFF альтернативы:
SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS datetime) –1
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, ”, CURRENT_TIMESTAMP)) –2
SELECT DATEADD(DAY, DATEDIFF(DAY, ‘20040101’, CURRENT_TIMESTAMP), ‘20040101’) –3
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)) –4

Если вы чувствуете, что ваши операции по производительности могут быть главным беспокойством, то я предлагаю вам тест. Здесь тестовый скрипт с обновленной производительностью, для всех четырех версий. Вариант 1 медленный, а 3 DATEDIFF альтернативы (2-4) довольно близки к победной 4 альтернативе. Я также предлагаю вам рассмотреть удобочитаемость, которая является делом вкуса.

Альтернативные DATEADD также обладают преимуществом гибкости. Вместо того, чтобы перепечатать предложения Hugo, я просто скопировал исходный текст Hugo в присланное мне письмо:

Версия, которая использует строковое преобразование, может быть использована для отделения времени от текущей даты. DATEADD/DATEDIFF версия может быть легко изменена, для получения начала текущего месяца, квартала или года. SELECT DATEADD(month, DATEDIFF(month, ‘20000101’, CURRENT_TIMESTAMP), ‘20000101’) или к отказу только от минут и секунд, или только секунд SELECT DATEADD(hour, DATEDIFF(hour, ‘20000101’, CURRENT_TIMESTAMP), ‘20000101’) (Примечание – когда речь идет о минутах, секундах или миллисекундах, функция DATEDIFF может вызвать переполнение целочисленного диапазона – базовая дата могла бы быть адаптирована к ожидаемому набору значений datetime). Также возможны дальнейшие изменения: Как получить последний день предыдущего месяца в последний день текущего месяца?

SELECT DATEADD(month, DATEDIFF(month, ‘20000101’, CURRENT_TIMESTAMP), ‘19991231’)
SELECT DATEADD(month, DATEDIFF(month, ‘20000101’, CURRENT_TIMESTAMP), ‘20000131’)

Как получить завтрашнюю дату (без времени)?

SELECT DATEADD(day, DATEDIFF(day, ‘20000101’, CURRENT_TIMESTAMP), ‘20000102’)

Как округлить datetime до ближайшего часа, или к ближайшему дню?

SELECT DATEADD(hour, DATEDIFF(hour, ‘20000101’, DATEADD(minute, 30, CURRENT_TIMESTAMP)), ‘20000101’)
SELECT DATEADD(day, DATEDIFF(day, ‘20000101’, DATEADD(hour, 12, CURRENT_TIMESTAMP)), ‘20000101’)

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

SELECT DATEADD(day, (DATEDIFF (day, ‘20000107’, CURRENT_TIMESTAMP) / 7) * 7, ‘20000107’)

или
SELECT DATEADD(day, (DATEDIFF (day, ‘20000108’, CURRENT_TIMESTAMP) / 7) * 7, ‘20000107’)

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

Совет: Всегда установить время в одно и то же значение

Если вы не заинтересованы в сегменте времени, и вы не можете использовать тип дата (date), вы можете установить временную часть в стандартное значение для всех строк. Я рекомендую 00:00:00.000. Допустим, вы хотите установить значение текущей даты, когда выполняется INSERT. Для этого используем конструкцию DEFAULT:

CREATE TABLE #dts(id INT IDENTITY, d_date DATETIME DEFAULT CONVERT(char(8), CURRENT_TIMESTAMP, 112))
INSERT #dts DEFAULT VALUES
SELECT * FROM #dts

Я использую код 112 в вышеприведенной функции CONVERT(), которая возвращает строку с указанием только даты в формате «без разделительных знаков» (Unseparated). Тем не менее, мы не защищены от явной установки значения и присвоения сегменту времени чего-то отличного от 00:00:00:000.

Мы можем сделать это с помощью CONSTRAINT-а:

ALTER TABLE #dts ADD CONSTRAINT dts_chk_d_date CHECK(CONVERT(char(12), d_date, 114) = ’00:00:00:000′)

Какие преимущества нам дает это выражение? Если мы желаем найти все строки, начиная с определенной даты, WHERE напишем таким образом:

WHERE dt = ‘20040305’

Этот метод можно рассматривать как более понятный и удобный для чтения. Кроме этого, если вы захотите объединить 2 таблицы, основываясь только на дне, теперь это можно сделать напрямую. Если у вас величина времени (часть содержащая время) изменяется, то придется использовать CONVERT в операциях JOIN, которые снова могут катастрофически влиять на производительность.
Вышеизложенная технология применима, если вам интересна только величина времени, в таком случае я рекомендую Вам всегда устанавливать саму дату как 1900-01-01.
Теперь, в SQL Server 2008 это не более чем отдельные типы date и time?

Дополнительные советы от Steve Kass

С осторожностью указывайте все даты в запросах типом smalldatetime, используя CAST или CONVERT. Отметим, что в колонке datetime SQL Server 7.0 (не менее) это может привести к непредвиденному результату.

Везде без веских причин и пояснений, даты сохраняются только типом datetime или smalldatetime, а не в виде строки. Если даты должны быть сохранены в строковом виде (существует несколько хороших причин для этого, но их мало), то используйте нейтрально-языковой формат и 24-часовое представление если также сохраняете и время, так чтобы строки могли использоваться везде и также при сортировке дат. Например, можно использовать “dt < ‘20030101’” в предложении WHERE и запрос не возвращает ожидаемые строки, потому как dt хранится в виде строки в формате yyyy-mm-dd (гггг-мм-дд).
В Enterprise Manager (и некоторых других инструментах и приложениях Microsoft) базовая дата это 1899-12-30. EM никогда не должен использоваться для записи данных, и редко для просмотра данных. Если вы используете EM для указания данных «только время», EM сохранит дату как 1899-12-30. Если вы действительно хотите использовать EM, укажите дату 1900-01-01 в явном виде. Отметим также, что EM отобразит только величину времени, если дата 1899-12-30, а не то, что вы ожидаете, если у вас в качестве стандартного значения даты используется 1900-01-01.

Почему 1753 ближайшая дата для DATETIME?

Хороший вопрос. Ответ: именно по историческим причинам. В современное время у нас (в том, что мы иногда называем западным миром) было два календаря: Юлианский и Григорианский календарь. Эти календари по нумерации дней отличались друг от друга (в зависимости от столетия на который вы посмотрите), поэтому, когда используемый Юлианский календарь заменили на Григорианский, было удалено от 10 до 13 дней. Великобритания сделала этот переход в 1752 году (дата 1752-09-02 стала 1752-09-14). Научное предположение, зачем Sybase выбрали 1753 в качестве ближайшей даты состоит в том, что при сохранении более ранней даты чем 1753, необходимо учитывать что это за страна, а также обработать этот 10-13дневный «прыжок». Поэтому они[Sybase] решили не позволять даты ранее 1753 года. Однако отметим, что прочие страны сделали это смещение позже 1752. Турция, например, это реализовала аж в 1927 году.
Будучи шведом, автор находит забавной ситуацию самой фантастической реализация именно у Швеции. Было решено пропускать день сдвига в течение 40 лет (с 1700 до 1740), и Швеция была бы в синхронизации с Григорианским календарем после 1740 (но в течение этого времени, не в синхронизации с другими странами). Но по некоторым причинам, в 1704 и 1708 годах день сдвига был не пропущен, а в 1712 который был високосным годом, они вставили дополнительный день (представьте себе рожденных 30 февраля!), и затем в 1753 году сделали сдвиг в течение дня, как и все остальные.

Список рекомендованной литературы

Это, безусловно, самая сложная из найденных документация о календарях:

Часто задаваемые вопросы о календарях, Claus Tøndering

Книга Richard T. Snodgrass “Разработка временно-ориентированных приложений баз данных в SQL”: содержит много сведений о представлении время-ориентированной информации в модели данных. И, конечно же, возможно использование этой дополнительной (исторической) информации в своих запросах SQL. Эта книга не издается, а на официальном сайте Ричарда (www.cs.arizona.edu/people/rts), вы бесплатно можете скачать ее в PDF формате.

Оригинал: The ultimate guide to the datetime datatypes

Перевод: Винчик Евгений

Работа с временем в SQL Firebird

Работа с временем важна и нужна. Вы можете сгенерировать дату средствами PHP или средствами SQL-языка. Например встроенная функция date(«d.m.Y») (день.месяц.год) вернет текущую дату ‘05.08.2015’. Такая конструкция date(«d.m.Y») подобна контекстной переменной CURRENT_DATE. Контекстная переменная CURRENT_TIME вернет текущее время ’20:18:29′. На PHP подобный результат можно получить при помощи такой конструкции date(«H:i:s»). Контекстная переменная CURRENT_TIMESTAMP вернёт текущую дату + время ‘05.08.2015 20:20:49’. Подобный результат на PHP даст date(«d.m.Y H:i:s»).


#выбрать текущую дату
SELECT CURRENT_DATE as date1 
FROM SHOP
#выбрать текущую времени
SELECT CURRENT_TIME as date1 
FROM SHOP
#выбрать текущей даты + время
SELECT CURRENT_TIMESTAMP as date1 
FROM SHOP

Дам вам один совет. Если вы в таблице хотите сохранить время создания записи (LAST_CHANGE), то применяйте контекстную переменную CURRENT_TIMESTAMP. Вы сможете выбирать из этого значения как дату, так и время. Вам поможет такая конструкция cast(last_change as time). Она выведет время.


#дата 21:16:29
select cast(last_change as time) as d1
from GM_SHOP 
ORDER BY LAST_CHANGE DESC

Эта конструкция выведет дату + время. Если вы из даты выведите данные в формате timestamp, то у вас отрежутся: секунды, минуты, часы.


#сортировка по дате в обратном порядке
select cast(last_change as timestamp) as d1
from GM_SHOP 
ORDER BY LAST_CHANGE DESC

Вы можете вывести только дату из timestamp.


#сортировка по дате в обратном порядке
select cast(last_change as date) as d1
from GM_SHOP 
ORDER BY LAST_CHANGE DESC

При выборке записей из базы данных вы можете использовать сортировку по дате.


#сортировка по дате в обратном порядке
select * from GM_SHOP 
ORDER BY LAST_CHANGE DESC

Вы можете вывести записи, которые созданы после определённой метки времени.


#выборка записей после заданной даты
SELECT * from GM_SHOP WHERE LAST_CHANGE > '11.08.2015'
#выборка записей за отрезок времени, 2 дня
SELECT * from GM_SHOP 
WHERE LAST_CHANGE > '11.08.2015' 
AND  LAST_CHANGE = '11.08.2015' 
AND  LAST_CHANGE  'NOW'

просмотры: 8971, уровень: лёгкий уровень, рейтинг: 4, дата: 2015-08-05 22:00:00

Комментарии:

Типы данных и функции даты и времени — SQL Server (Transact-SQL)

  • 7 минут на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL AzureAzure SQL Managed InstanceAzure Synapse Analytics

В разделах этого раздела рассматриваются все типы данных и функции даты и времени Transact-SQL.

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

Типы данных даты и времени Transact-SQL перечислены в следующей таблице:

С С С
Тип данных Формат Диапазон Точность Размер памяти (байты) Определяемая пользователем точность до долей секунды Смещение часового пояса
время чч: мм: сс [.nnnnnnn] 00: 00: 00.0000000 по 23: 59: 59.9999999 100 наносекунд от 3 до 5 Есть Нет
дата ГГГГ-ММ-ДД 0001-01-01 по 9999-12-31 1 день 3 Нет Нет
малое время ГГГГ-ММ-ДД чч: мм: сс 1900-01-01 по 2079-06-06 1 минута 4 Нет Нет
дата и время ГГГГ-ММ-ДД чч: мм: сс [.nnn] 1753-01-01 по 9999-12-31 0,00333 секунды 8 Нет Нет
datetime2 ГГГГ-ММ-ДД чч: мм: сс [.nnnnnnn] 0001-01-01 00: 00: 00.0000000 по 9999-12-31 23: 59: 59.9999999 100 наносекунд от 6 до 8 Есть Нет
datetimeoffset ГГГГ-ММ-ДД чч: мм: сс [.nnnnnnn] [+ | -] чч: мм 0001-01-01 00:00:00.От 0000000 до 9999-12-31 23: 59: 59.9999999 (по всемирному координированному времени) 100 наносекунд 8-10 Есть Есть

Примечание

Тип данных версии строки Transact-SQL не является типом данных даты или времени. Отметка времени является устаревшим синонимом версии строки .

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

В следующих таблицах перечислены функции даты и времени Transact-SQL. См. Детерминированные и Недетерминированные функции для получения дополнительной информации о детерминизме.

Функция, возвращающая системные значения даты и времени

Transact-SQL извлекает все системные значения даты и времени из операционной системы компьютера, на котором работает экземпляр SQL Server.

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

SQL Server 2019 (15.x) получает значения даты и времени с помощью Windows API GetSystemTimeAsFileTime (). Точность зависит от аппаратного обеспечения компьютера и версии Windows, на которой работает экземпляр SQL Server.Этот API имеет фиксированную точность 100 наносекунд. Используйте Windows API GetSystemTimeAdjustment () для определения точности.

Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм
SYSDATETIME SYSDATETIME () Возвращает значение datetime2 (7) , содержащее дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемое значение не включает смещение часового пояса. datetime2 (7) Недетерминированный
SYSDATETIMEOFFSET SYSDATETIMEOFFSET () Возвращает значение datetimeoffset (7) , содержащее дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемое значение включает смещение часового пояса. datetimeoffset (7) Недетерминированный
SYSUTCDATETIME SYSUTCDATETIME () Возвращает значение datetime2 (7) , содержащее дату и время компьютера, на котором запущен экземпляр SQL Server.Функция возвращает значения даты и времени в формате UTC (всемирное координированное время). datetime2 (7) Недетерминированный
Системные функции даты и времени с более низкой точностью
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм
CURRENT_TIMESTAMP CURRENT_TIMESTAMP Возвращает значение datetime , содержащее дату и время компьютера, на котором запущен экземпляр SQL Server.Возвращаемое значение не включает смещение часового пояса. дата и время Недетерминированный
GETDATE GETDATE () Возвращает значение datetime , содержащее дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемое значение не включает смещение часового пояса. дата и время Недетерминированный
GETUTCDATE GETUTCDATE () Возвращает значение datetime , содержащее дату и время компьютера, на котором запущен экземпляр SQL Server.Функция возвращает значения даты и времени в формате UTC (всемирное координированное время). дата и время Недетерминированный

Функции, возвращающие дату и время

Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм
ИМЯ DATENAME ( datepart , date ) Возвращает символьную строку, представляющую указанный datepart указанной даты. nvarchar Недетерминированный
DATEPART DATEPART ( datepart , date ) Возвращает целое число, представляющее указанную дату , часть указанной даты . внутренний Недетерминированный
ДЕНЬ ДЕНЬ ( дата ) Возвращает целое число, представляющее дневную часть указанной даты . внутренний Детерминированный
МЕСЯЦ МЕСЯЦ ( дата ) Возвращает целое число, представляющее часть месяца указанной даты . внутренний Детерминированный
ГОД ГОД ( дата ) Возвращает целое число, представляющее часть года указанной даты . внутренний Детерминированный

Функции, возвращающие значения даты и времени из своих частей

Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм
ХАРАКТЕРИСТИКИ DATEFROMPARTS ( год , месяц , день ) Возвращает дату значение для указанного года, месяца и дня. дата Детерминированный
DATETIME2FROMPARTS DATETIME2FROMPARTS ( год , месяц , день , час , минут , секунд , дроби , точность ) Возвращает значение datetime2 для указанной даты и времени с указанной точностью. datetime2 ( точность ) Детерминированный
ХАРАКТЕРИСТИКИ DATETIMEFROMPARTS ( год , месяц , день , час , минута , секунд , миллисекунды ) Возвращает значение datetime для указанной даты и времени. дата и время Детерминированный
ВРЕМЯ ДАТЫ НАБОРА ИЗДЕЛИЙ DATETIMEOFFSETFROMPARTS ( год , месяц , день , час , минут , секунд , дробей , час_смещение , минут_смещение ) Возвращает значение datetimeoffset для указанной даты и времени с указанными смещениями и точностью. datetimeoffset ( точность ) Детерминированный
МАЛЫЕ ДАННЫЕ ИЗДЕЛИЯ SMALLDATETIMEFROMPARTS ( год , месяц , день , час , минут ) Возвращает значение smalldatetime для указанной даты и времени. малое время Детерминированный
TIMEFROMPARTS TIMEFROMPARTS ( час , минут , секунд , дроби , точность ) Возвращает значение time для указанного времени с указанной точностью. время ( точность ) Детерминированный

Функции, возвращающие значения разницы даты и времени

Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм
РАЗНДАТ DATEDIFF ( datepart , startdate , enddate ) Возвращает число границ даты или времени datepart , пересеченных между двумя указанными датами. внутренний Детерминированный
DATEDIFF_BIG DATEDIFF_BIG ( datepart , startdate , enddate ) Возвращает число границ даты или времени datepart , пересеченных между двумя указанными датами. bigint Детерминированный

Функции, изменяющие значения даты и времени

Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм
ДАТА ДОБАВИТЬ DATEADD ( datepart , number , date ) Возвращает новое значение datetime , добавляя интервал к указанной datepart указанной date . Тип данных дата аргумент Детерминированный
EOMONTH EOMONTH ( start_date [, month_to_add ]) Возвращает последний день месяца, содержащий указанную дату, с необязательным смещением. Возвращаемый тип — это тип аргумента start_date, или, альтернативно, тип данных date . Детерминированный
ПЕРЕКЛЮЧАТЕЛЬ SWITCHOFFSET ( DATETIMEOFFSET , часовой_зон ) SWITCHOFFSET изменяет смещение часового пояса значения DATETIMEOFFSET и сохраняет значение UTC. datetimeoffset с дробной точностью DATETIMEOFFSET Детерминированный
TODATETIMEOFFSET TODATETIMEOFFSET ( выражение , часовой_ пояс ) TODATETIMEOFFSET преобразует значение datetime2 в значение datetimeoffset. TODATETIMEOFFSET интерпретирует значение datetime2 по местному времени для указанного time_zone. datetimeoffset с дробной точностью аргумента datetime Детерминированный

Функции, устанавливающие или возвращающие функции формата сеанса

Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм
@@ DATEFIRST @@ DATEFIRST Возвращает текущее значение для сеанса SET DATEFIRST. tinyint Недетерминированный
УСТАНОВИТЬ ДАТУ ПЕРВАЯ УСТАНОВИТЬ ПЕРВУЮ ДАТУ { номер | @ номер_вар } Устанавливает первый день недели на число от 1 до 7. Не применимо Не применимо
НАБОР DATEFORMAT SET DATEFORMAT { формат | @ format_var } Устанавливает порядок частей даты (месяц / день / год) для ввода данных datetime или smalldatetime . Не применимо Не применимо
@@ ЯЗЫК @@ LANGUAGE Возвращает название текущего используемого языка. @@ LANGUAGE не является функцией даты или времени. Однако настройка языка может повлиять на вывод функций даты. Не применимо Не применимо
УСТАНОВИТЬ ЯЗЫК УСТАНОВИТЬ ЯЗЫК {[N] язык | @ language_var } Устанавливает языковую среду для сеанса и системных сообщений.УСТАНОВИТЬ ЯЗЫК не является функцией даты или времени. Однако настройка языка влияет на вывод функций даты. Не применимо Не применимо
sp_helplanguage sp_helplanguage [[ @language = ] язык ] Возвращает информацию о форматах даты для всех поддерживаемых языков. sp_helplanguage не является хранимой процедурой даты или времени. Однако настройка языка влияет на вывод функций даты. Не применимо Не применимо

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

Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм
ISDATE ISDATE ( выражение ) Определяет, имеет ли входное выражение datetime или smalldatetime допустимое значение даты или времени. внутренний ISDATE является детерминированным и используется только с функцией CONVERT, когда указан параметр стиля CONVERT и когда style не равен 0, 100, 9 или 109.
Тема Описание
ФОРМАТ Возвращает значение, отформатированное с использованием указанного формата и необязательного языка и региональных параметров. Используйте функцию FORMAT для форматирования даты / времени и числовых значений в виде строк с учетом локали.
CAST и преобразование (Transact-SQL) Предоставляет информацию о преобразовании значений даты и времени в строковые литералы и обратно, а также в другие форматы даты и времени.
Запись международных операторов Transact-SQL Содержит рекомендации по переносимости баз данных и приложений баз данных, которые используют операторы Transact-SQL с одного языка на другой или поддерживают несколько языков.
Скалярные функции ODBC (Transact-SQL) Предоставляет информацию о скалярных функциях ODBC, доступных для использования в операторах Transact-SQL.Сюда входят функции даты и времени ODBC.
В ЧАСОВОЙ ПОЯС (Transact-SQL) Обеспечивает преобразование часового пояса.

См. Также

Обзор функций даты SQL Server

На этой странице перечислены наиболее часто используемые функции даты SQL Server, которые позволяют эффективно обрабатывать данные о дате и времени.

Возвращает текущую дату и время

Функция Описание
CURRENT_TIMESTAMP Возвращает текущую системную дату и время без части часового пояса.
GETUTCDATE Возвращает часть даты в виде целого числа.
GETDATE Возвращает текущую системную дату и время операционной системы, в которой работает SQL Server.
SYSDATETIME Возвращает текущую системную дату и время с большей точностью до долей секунды, чем функция GETDATE ().
SYSUTCDATETIME Возвращает текущую системную дату и время в формате UTC.
SYSDATETIMEOFFSET Возвращает текущую системную дату и время с указанием часового пояса.

Возвращение даты и времени Части

Функция Описание
DATENAME Возвращает часть даты в виде строки символов
DATEPART Возвращает дату часть даты в виде целого числа
ДЕНЬ Возвращает день указанной даты как целое число
МЕСЯЦ Возвращает месяц указанной даты как целое число
ГОД Возвращает год даты как целое число.

Возвращение разницы между двумя датами

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

Изменение дат

Создание даты и времени из их частей

Проверка значений даты и времени

Функция Описание
ISDATE Проверьте, является ли значение допустимой датой, время или значение datetime

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

В этой статье мы узнаем о функциях даты и времени в SQL Server.Эти функции работают с заданной датой и временем. Эти функции являются предварительно определенными функциями SQL.

Типы функций DateTime

  1. Getdate
  2. День
  3. Месяц
  4. Год
  5. Имя даты
  6. Dateadd
  7. Datediff
  8. Datepart
Функция

Getdate date () возвращает текущую дату

Getdate () .

Синтаксис

Day ()

Эта функция возвращает значение дня с заданной даты

Синтаксис

  1. выберите ДЕНЬ (‘2018/10/31’)

Месяц

Эта функция возвращает значение месяца с заданной даты

Синтаксис

  1. выберите Месяц (‘2018/10/31’)

Год ()

Эта функция возвращает значение года с заданной даты

Синтаксис

  1. выберите год (‘2018/10/31’)

Datename ()

Возвращает имя дня и месяца заданного выражения даты.

Пример

  1. выберите DATENAME (DW, ‘2018/10/31’)
  2. выберите DATENAME (MM, ‘2018/10/31’)

Результат

Dateadd ()

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

синтаксис

  1. select DATEADD (‘День / месяц / год’, ‘Номер числа, который нужно добавить’, ‘Дата’)

Пример

  1. select DATEADD (dd, 5, getdate ())
  2. выберите DATEADD (MM, 1, getdate ())
  3. выберите DATEADD (YYYY, 1, getdate ())

Результат

Datediff ()

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

Синтаксис

  1. select dateiff (‘День / месяц / год’, ‘startDate’, ‘endDate’)

Пример

  1. select DATEDIFF (dd, ‘2017/2/3’, ‘2018 / 3/5 ‘) в качестве TotalDays
  2. выберите DATEDIFF (MM,’ 2017/2/3 ‘,’ 2018/3/5 ‘) в качестве TotalMonths
  3. выберите DATEDIFF (YYYY,’ 2017/2/3 ‘,’ 2018 / 3/5 ‘) в качестве Totalyear
  4. выберите DATEDIFF (WK,’ 2017/2/3 ‘,’ 2018/3/5 ‘) в качестве TotalWeeks
  5. выберите DATEDIFF (HH,’ 2017/2/3 ‘,’ 2018 / 3/5 ‘) В качестве TotalHours
  6. выберите DATEDIFF (MI,’ 2017/2/3 ‘,’ 2018/3/5 ‘) В качестве TotalMin

Результат

Пример 1

Как мы найти разницу между значениями строки столбца.

Создание таблицы,

  1. createtable Userdate (идентификатор int, дата рождения)

Добавить значение в таблицу,

  1. вставить в значения Userdate (‘1994/12/30’)
  2. вставить в значения Userdate (‘1995/7/4’)
  3. вставить в значения Userdate (‘2000/9/1’)
  4. вставить в значения Userdate (‘1999/12/30’)

Теперь найдите разницу. Используйте этот запрос:

  1. выберите a1.Дата рождения, dateiff (день, a1.birthdate, a2.birthdate) как DobDiff из Userdate a1 внутреннего соединения Userdate a2 на a2.id = a1.id + 1
Результат

Datepart ()

Он возвращает значения дня, месяца и года из заданного выражения даты

Пример

  1. выберите DATEPART (day, getdate ()) как Days
  2. выберите DATEPART (MM , getdate ()) в качестве месяца
  3. выберите DATEPART (YYYY, getdate ()) в качестве года
  4. выберите DATEPART (WK, getdate ()) в качестве слабых
  5. выберите DATEPART (HH, getdate ()) в качестве часов

Результат

Сводка

В этой статье мы узнали о функциях datetime в SQL Server.Функции DateTime — это предварительно определенные функции SQL.

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

1. Функции даты SQL

Сегодня в этом руководстве по функциям даты SQL мы изучим функции даты и времени в SQL . Кроме того, мы обсудим функции даты MySQL, в которых мы увидим функции даты SQL Server, метку времени MySQL до даты, функцию времени SQL. Также мы обсуждаем типы данных SQL Date Functions и SQL, работающий с Date.

Итак, приступим к уроку SQL Date Functions Tutorial.

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

2. Функции даты и времени в SQL

Существует много важных функций даты SQL, хотя с функциями времени SQL это усложняется.
Давайте подробно изучим функции даты и времени в SQL.
Знаете ли вы о типах данных SQL

3. Функции даты MySQL

Следующие функции даты SQL важны, указанные в таблице.

Функция Описание
СЕЙЧАС () Эта функция используется для возврата текущей даты и времени.
CURDATE () Эта функция используется для возврата текущей даты.
CURTIME () Эта функция используется для возврата текущего времени.
DATE () Эта функция используется для извлечения части даты из даты или выражения даты / времени.
EXTRACT () Эта функция используется для возврата одной части даты / времени.
DATE_ADD () Эта функция используется для добавления указанного временного интервала к дате.
DATE_SUB () Эта функция используется для вычитания указанного временного интервала из даты.
DATEDIFF () Эта функция используется для возврата количества дней между двумя датами.
DATE_FORMAT () Эта функция используется для отображения данных даты / времени в различных форматах.

Знаете ли вы о нулевых значениях в SQL

a. Функции даты SQL Server

В следующей таблице показаны наиболее важные функции даты в SQL Server —

Функция Описание
GETDATE () Возвращает текущую дату и время.
DATEPART () Возвращает одну часть даты / времени.
DATEADD () Добавляет или вычитает указанный временной интервал из даты.
РАЗНДАТ () Возвращает время между двумя датами.
ПРЕОБРАЗОВАТЬ () Отображает данные даты и времени в различных форматах.

б. Типы данных даты SQL

В MySQL есть следующие типы данных, которые используются для хранения значения данных / времени в базе данных:
Взгляните на SQL-инъекции

  • ДАТА — Формат ГГГГ-ММ -DD ​​
  • DATETIME — Формат: YYYY-MM-DD HH: MI: SS
  • TIMESTAMP — Формат : YYYY-MM-DD HH: MI: SS
  • YEAR — Формат YYYY или YY

В SQL Server Date есть следующие типы данных, которые используются для хранения значения данных / времени в базе данных:

  • SQL DATE — Формат YYYY-MM-DD
  • DATETIME — Format: YYYY -MM-DD HH: MI: SS
  • SMALLDATETIME — Формат: YYYY-MM-DD HH: MI: SS
  • SQL TIMESTAMP — Формат: уникальный номер

Примечание: Когда мы создаем новая таблица a типы данных выбираются для нового столбца в базе данных.

г. SQL Работа с датами

Предположим, у нас есть следующая таблица «Заказы» в SQL Dates:

OrderId ProductName OrderDate
1 Geitost 2008-11 -11
2 Камамбер Пьеро 2008-11-09
3 Моцарелла ди Джованни 2008-11-11
4 Маскарпоне Фабиоли 2008-10- 29

Теперь, когда мы хотим выбрать записи с OrderDate «2008-11-11» из приведенной выше таблицы.
Давайте обсудим транзакцию SQL Server
Мы используем следующий оператор SELECT:

 SELECT * FROM Orders WHERE OrderDate = '2008-11-11'; 

Ниже приведен результат для функций дат SQL

OrderId ProductName OrderDate
1 Geitost 2008-11-11
3 Моцарелла ди Джованни 2008-11-11

Теперь предположим, что таблица «Заказы» выглядит следующим образом (обратите внимание на компонент времени в столбце «Дата заказа»):

OrderId ProductName OrderDate
1 Geitost 2008-11-11 13:23:44
2 Camembert Pierrot 2008-11-09 15:45:21
3 Моцарелла ди Джованни 2008-11-11 11:12:01
4 Маскарпоне Фабиоли 29.10.2008 14:56:59

Если мы используем тот же оператор SELECT, что и выше:

 SELECT * FROM Orders WHERE OrderDate = '2008-11-11'; 

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

4. Заключение — функции SQL DateTime

Таким образом, в этом руководстве по функциям даты SQL мы изучили функции даты и времени в SQL. Кроме того, мы обсудили функции даты MySQL, в которых мы узнали функции даты SQL Server, метку времени MySQL до даты, функцию времени SQL. Также мы видели типы данных SQL Date Functions и SQL, работающий с Date. Тем не менее, если у вас есть какие-либо вопросы относительно функций даты SQL, вы можете задать их на вкладке комментариев.
См. Также —
Автоинкремент SQL
Для справки

Помощник по SQL Server — Функции даты и времени

Функция Описание
CURRENT_TIMESTAMP Возвращает значение datetime2 (7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Смещение часового пояса не включается.

Синтаксис:

 CURRENT_TIMESTAMP 
ДАТА ДОБАВИТЬ Возвращает новое значение datetime на основе добавления интервала к указанной дате.

Синтаксис:

 ДОБАВЛЕНИЕ ДАТЫ (datepart, number, date) 
РАЗНДАТ Возвращает количество границ даты и времени, пересеченных между двумя указанными датами.

Синтаксис:

 РАЗНДАТ (datepart, startdate, enddate) 
ИМЯ Возвращает символьную строку, представляющую указанную часть указанной даты.

Синтаксис:

 ИМЯ ДАТЫ (datepart, date) 
DATEPART Возвращает целое число, представляющее указанную часть указанной даты.

Синтаксис:

 DATEPART (дата, часть, дата) 
ДЕНЬ Возвращает целое число, представляющее день, часть указанной даты.

Синтаксис:

 ДЕНЬ (дата) 
GETDATE Возвращает текущую системную дату и время в стандартном внутреннем формате SQL Server для значений datetime.

Синтаксис:

 GETDATE () 
GETUTCDATE Возвращает значение datetime, представляющее текущее время в формате UTC (всемирное координированное время или среднее время по Гринвичу). Текущее время в формате UTC получается из текущего местного времени и настройки часового пояса в операционной системе компьютера, на котором запущен экземпляр Microsoft SQL Server.

Синтаксис:

 GETUTCDATE () 
ISDATE Определяет, является ли входное выражение datetime или smalldatetime допустимым значением даты или времени.

Синтаксис:

 ISDATE (выражение) 
МЕСЯЦ Возвращает целое число, которое представляет часть месяца указанной даты.

Синтаксис:

 МЕСЯЦ (дата) 
ГОД Возвращает целое число, представляющее часть года указанной даты.

Синтаксис:

 ГОД (дата) 

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

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

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

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

GETDATE & GETUTCDATE #

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

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

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

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

Вы также можете использовать `TODATETIMEOFFSET ‘для работы со стандартным GETDATE .

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

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

Однако вы можете ограничить его вывод датой или частью времени с помощью функций CONVERT или FORMAT .

DATEADD #

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

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

Для этого требуются три вещи:

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

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

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

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

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

Очень похожий расчет даты также доступен с DATEADD ( ) Функция AMPScript.В зависимости от варианта использования подумайте, хотите ли вы управлять датой в расширении данных или только в сообщении.

DATEPART #

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

Найти всех подписчиков, которые присоединились к 2020

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

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

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

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

Детали даты #

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

Часть даты Сокращения
ГОД гг, гггг
КВАРТАЛ qq, q
МЕСЯЦ мм, м
DAYOFYEAR , y
DAY dd, d
WEEK wk, ww
WEEKDAY dw, w
HOUR hh
MINUTE mi, n
СЕКУНДА сс, с
МИЛЛИСЕКУНДА мс
МИКРОСЕКУНД мкс
НС, НС

Y18 ДЕНЬ ДЕНЬ 918 считаются одной и той же частью даты и возвращают одинаковое значение.

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

РАЗНДАТ #

Функция РАЗНДАТ вычисляет разницу между двумя датами.

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

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

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

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

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

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

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

DATENAME #

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

Захватить полное имя дня

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

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

AT TIME ZONE #

Функция AT TIME ZONE предоставляет две невероятные функции:

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

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

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

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

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

Вы можете быстро исправить это с помощью AT TIME ZONE . Когда вы применяете эту функцию к datetime без часового пояса, она будет предполагать, что вы ее определяете, и возвращает значение с соответствующим смещением.

Магия перехода на летнее время

В приведенном выше фрагменте мы КОНВЕРТИРУЕМ даты в datetime без смещения.Затем функция AT TIME ZONE добавляет смещение и рассчитывает изменение смещения для летнего времени.

AT TIME ZONE vs TODATETIMEOFFSET #

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

Salesforce Marketing Cloud использует центральное стандартное время (UTC-6) без изменений между стандартным и летним временем.Это значит, что отличная функция AT TIME ZONE работает против нас.

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

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

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

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

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

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

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

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

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

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

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

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

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

Нет волшебства перехода на летнее время, но все еще меняет часовой пояс

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

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

Не беспокойтесь о названиях летнего времени - AT TIME ZONE используйте стандартные и позаботится о летнем времени.

FORMAT with Dates #

FORMAT работает только с datetime и числовыми значениями, выводит строку и требует:

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

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

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

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

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

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

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

Спецификатор формата Описание Пример (для культуры США)
'd' Краткая дата 30.10.2020
'D' Длинная дата Пятница, 30 октября 2020 г.
'm' / 'M' День месяца 30 октября
'y' / 'Y' Год Месяц Октябрь 2020
't' Кратковременно 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' Fu ll long time Пятница, 30 октября 2020 г. 14:15:30
'r' / 'R' RFC1123 Пт, 30 октября 2020 г. 14:15:30 GMT

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

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

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

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

Спецификатор формата Описание Пример (для культуры США)
'd' День месяца 1
'dd' День месяца с отсчетом от нуля 01
'ddd' Сокращенный день месяца Пн
'dddd' Полный день месяца Понедельник
'M' Месяц 1
'MM' Месяц с нулевым отсчетом 01
'MMM' Сокращенный месяц Янв
'MMMM' Полный месяц Январь
'yy' Последние две цифры числа th e год 20
'yyyy' Год из четырех цифр 2020
'h' Час (12-часовой формат) 1
'hh' Zero- базовый час (12-часовой формат) 01
'tt' Обозначение AM / PM AM
'H' Час (24-часовой формат) 13
'HH' Час с отсчетом от нуля (24-часовой формат) 01
'м' Минуты 1
'мм' Минуты с отсчетом от нуля 01
's' Секунды 1
'ss' Секунды с отсчетом от нуля 01
':' Разделитель времени ':'
'/' Разделитель даты '/'

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

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

Приведенный выше запрос вернет суббота (30 октября 2020 г.) - 02:15 .

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

AMPScript использует очень похожее настраиваемое форматирование с функциями FORMAT () и FORMATDATE () . В зависимости от варианта использования подумайте, хотите ли вы управлять визуальным представлением даты в расширении данных или только в сообщении.

ДАТА / ВРЕМЯ Функции в SQL. Учебное пособие по использованию CAST, EXTRACT… | Джейсон Ли

Затем мы можем взглянуть на извлечение определенного формата из метки времени. Цель состоит в том, чтобы извлечь часть из метки времени. Например, если нам нужен только месяц с даты 10.12.2018, мы получим декабрь (12).

Давайте посмотрим на EXTRACT синтаксис

 EXTRACT (часть ОТ даты) 

Мы указываем тип извлечения, который мы хотим, как часть , а затем источник, который будет извлечен, , дата . EXTRACT - это инструмент импорта для анализа данных временных рядов. Это помогает вам изолировать группы в ваших временных метках для агрегирования данных на основе точного времени. Например, если магазин по аренде автомобилей хотел найти самый загруженный пункт проката HOUR по ПОНЕДЕЛЬНИКИ каждые MAY , вы можете сделать это с помощью EXTRACT . Вы можете детализировать детали и увидеть более ценную информацию.

Предположим, мы запускаем NOW () , а наша отметка времени - 2020-03-29 00:27:51.677318 + 00: 00 , мы можем использовать EXTRACT , чтобы получить следующее.

 ВЫБРАТЬ ВЫПИСКУ 
(МИНУТА С ТЕПЕРЬ ()) КАК МИНУТА,
ЭКСТРАКТ (ЧАС С ТЕПЕРЬ ()) КАК ЧАС,
ВЫПИСКА (ДЕНЬ ОТ ТЕПЕРЬ ()) КАК ДЕНЬ,
ЭКСТРАКТ (НЕДЕЛЮ С ТЕПЕРЬ ()) КАК НЕДЕЛЮ ,
ЭКСТРАКТ (МЕСЯЦ С СЕЙЧАС ()) КАК МЕСЯЦ,
ЭКСТРАКТ (ГОД С ТЕПЕРЬ ()) КАК ГОД,
ЭКСТРАКТ (ВНИЗ С СЕЙЧАС ()) КАК ДЕНЬ_OF_WEEK,
ЭКСТРАКТ (ДОЛЖНОСТЬ С ТЕПЕРЬ ()) КАК ДЕНЬ_O13_ГОД EXTRACT (QUARTER FROM FROM NOW ()) AS QUARTER,
EXTRACT (TIMEZONE FROM NOW ()) AS TIMEZONE
Output

Мы видим, что можем подробно описать, как мы хотим извлечь информацию из наших временных меток.Примечание - DOW - День недели с воскресенья (0) по субботу (6).

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

 ВЫБРАТЬ ВЫПЛАТУ 
(ЧАС ОТ ДАТЫ АРЕНДЫ) КАК ЧАС,
СЧЕТ (*) КАК АРЕНДА
ОТ АРЕНДЫ
ГДЕ
ВЫПИСКА (ВЫПЛАТА С ДАТЫ АРЕНДЫ) = 1 И
ВЫПЛАТА (МЕСЯЦ ОТ ДАТЫ ЗАКАЗА
) = ПО УДАЛЕНИЮ АРЕНДЫ

Помните, что нам нужна самая загруженная аренда ЧАС ПОНЕДЕЛЬНИКОВ каждые МАЯ .Во-первых, мы используем EXTRCT в SELECT , чтобы заявить, что нам нужен только HOUR и всего COUNT . Затем мы передаем две функции EXTRACT для в предложении WHERE , чтобы отфильтровать только MONDAYS и MAY . В таблице ниже 11 часов утра - это самое популярное время аренды каждый понедельник в мае, всего их арендуют 11 раз.

Выход .