Типы данных и функции даты и времени (Transact-SQL)
В разделах этой статьи представлен обзор всех типов данных и функций даты и времени Transact-SQL.
- Типы данных даты и времени
- Функции даты и времени
- Функции, возвращающие значения системной даты и времени
- Функции, возвращающие компоненты даты и времени
- Функции, возвращающие значения даты и времени из их компонентов
- Функции, возвращающие значения разности даты и времени
- Функции, изменяющие значения даты и времени
- Функции, устанавливающие или возвращающие функции формата сеанса
- Функции, проверяющие значения даты и времени
Типы данных даты и времени
Типы данных даты и времени Transact-SQL перечислены в следующей таблице:
Тип данных Формат Диапазон Точность Объем памяти (в байтах) Определяемая пользователем точность в долях секунды Смещение часового пояса time чч:мм:сс[.ннннннн] От 00:00:00.0000000 до 23:59:59.9999999 100 наносекунд от 3 до 5 Да Нет date ГГГГ-ММ-ДД От 0001-01-01 до 31.12.99 1 день 3 Нет Нет smalldatetime ГГГГ-ММ-ДД чч:мм:сс От 01.01.1900 до 06.06.2079 1 минута 4 нет Нет datetime ГГГГ-ММ-ДД чч:мм:сс[.ннн] От 01.01.1753 до 31.12.9999 0,00333 секунды 8 Нет Нет datetime2 ГГГГ-ММ-ДД чч:мм:сс[.ннннннн] От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 100 наносекунд От 6 до 8 Да Нет datetimeoffset ГГГГ-ММ-ДД чч:мм:сс[.ннннннн] [+|-]чч:мм От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 (время в формате UTC) 100 наносекунд От 8 до 10 Да Да Тип данных Transact-SQL rowversion не относится к типам данных даты и времени. Тип данных timestamp является устаревшим синонимом rowversion.
Функции даты и времени
В следующих таблицах приводятся функции даты и времени Transact-SQL. Дополнительные сведения о детерминизме см. в статье Детерминированные и недетерминированные функции.
Функции, возвращающие значения системной даты и времени
Transact-SQL наследует все значения системной даты и времени от операционной системы компьютера, на котором работает экземпляр SQL Server.
Высокоточные функции системной даты и времени
Начиная с SQL Server 2008 (10.0.x) ядро СУБД получает значения даты и времени с помощью API Windows GetSystemTimeAsFileTime(). Точность зависит от физического оборудования и версии Windows, в которой запущен экземпляр SQL Server. Точность возвращаемых значений этого API-интерфейса задана равной 100 нс. Точность может быть определена с помощью метода GetSystemTimeAdjustment() API-интерфейса Windows.
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм 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. Возвращаемое значение не содержит смещение часового пояса. datetime Недетерминированная GETDATE GETDATE ( ) Возвращает значение типа datetime, которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемое значение не содержит смещение часового пояса. datetime Недетерминированная GETUTCDATE GETUTCDATE ( ) Возвращает значение типа datetime, которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Функция возвращает значения даты и времени в формате UTC. datetime Недетерминированная Функции, возвращающие компоненты даты и времени
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм DATE_BUCKET DATE_BUCKET ( datepart, number, date, origin ) Возвращает значение даты и времени, соответствующее началу каждого контейнера даты и времени, из временной метки, определенной параметром origin или исходным значением по умолчанию 1900-01-01 00:00:00.000 , если параметр orgin не указан. Тип возвращаемого значения зависит от типа аргумента, переданного в параметре date. Недетерминированная DATENAME DATENAME ( datepart, date ) Возвращает строку символов, представляющую указанную часть datepart заданного типа date. nvarchar Недетерминированная DATEPART DATEPART ( datepart, date ) Возвращает целое число, представляющее указанную часть datepart заданного типа date. int Недетерминированная DATETRUNC DATETRUNC ( datepart, date ) Эта функция возвращает входную дату date, усеченную до указанной части datepart. Тип возвращаемого значения зависит от типа аргумента, переданного в параметре date. Недетерминированная DAY DAY ( date ) Возвращает целое число, представляющее часть дня указанного типа date. int Детерминированный MONTH MONTH ( date ) Возвращает целое число, представляющее часть месяца указанного типа date. int Детерминированный YEAR YEAR ( date ) Возвращает целое число, представляющее часть года указанного типа date. int Детерминированный Функции, возвращающие значения даты и времени из их компонентов
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм DATEFROMPARTS DATEFROMPARTS ( year, month, day ) Возвращает значение date, соответствующее указанному числу, месяцу и году. date Детерминированный DATETIME2FROMPARTS DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision) Возвращает значение datetime2, соответствующее указанной дате и времени с заданной точностью. datetime2(precision) Детерминированный DATETIMEFROMPARTS DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds) Возвращает значение datetime, соответствующее указанной дате и времени. datetime Детерминированный DATETIMEOFFSETFROMPARTS DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision) Возвращает значение datetimeoffset для указанных даты и времени с указанными смещением и точностью. datetimeoffset(precision) Детерминированный SMALLDATETIMEFROMPARTS SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) Возвращает значение smalldatetime, соответствующее указанной дате и времени. smalldatetime Детерминированный TIMEFROMPARTS TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) Возвращает значение time, соответствующее указанному времени с заданной точностью. time(precision) Детерминированный Функции, возвращающие значения разности даты и времени
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм DATEDIFF DATEDIFF ( datepart, startdate, enddate ) Возвращает количество границ даты или времени datepart, пересекающихся между двумя указанными датами. int Детерминированный DATEDIFF_BIG DATEDIFF_BIG ( datepart, startdate, enddate ) Возвращает количество границ даты или времени datepart, пересекающихся между двумя указанными датами. bigint Детерминированный Функции, изменяющие значения даты и времени
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм DATEADD DATEADD (datepart, number, date ) Возвращает новое значение datetime, добавляя интервал к указанной части datepart заданной даты date. Тип данных аргумента date Детерминированный EOMONTH EOMONTH ( start_date [, month_to_add ] ) Возвращает последний день месяца, содержащего указанную дату, с необязательным смещением. Тип возвращаемого значения — это тип аргумента start_date или тип данных date. Детерминированный SWITCHOFFSET SWITCHOFFSET (DATETIMEOFFSET, time_zone) Функция SWITCHOFFSET изменяет смещение часового пояса для значения DATETIMEOFFSET и сохраняет значение UTC. Значение datetimeoffset с точностью в долях секунд, заданной в аргументе DATETIMEOFFSET Детерминированный TODATETIMEOFFSET TODATETIMEOFFSET (expression, time_zone) TODATETIMEOFFSET преобразует значение типа datetime2 в значение типа datetimeoffset. Функция TODATETIMEOFFSET преобразует значение datetime2 в местное время для указанного time_zone. Значение datetimeoffset с точностью в долях секунд, заданной в аргументе datetime Детерминированный Функции, устанавливающие или возвращающие функции формата сеанса
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм @@DATEFIRST @@DATEFIRST Возвращает текущее значение параметра SET DATEFIRST для сеанса. tinyint Недетерминированная SET DATEFIRST SET DATEFIRST < number | @number_var > Устанавливает первый день недели в виде числа от 1 до 7. Неприменимо Неприменимо SET DATEFORMAT SET DATEFORMAT < format | @format_var > Задает порядок составляющих даты (месяц/день/год) для ввода данных типа datetime или smalldatetime. Неприменимо Неприменимо @@LANGUAGE @@LANGUAGE Возвращает название использующегося в настоящий момент языка. @@LANGUAGE не является функцией даты или времени. Однако на данные, выводимые функциями даты, могут повлиять настройки языка. Неприменимо Неприменимо SET LANGUAGE SET LANGUAGE < [ N ] ‘language‘ | @language_var > Устанавливает языковую среду сеанса и системных сообщений. SET LANGUAGE не является функцией даты или времени. Однако на данные, выводимые функциями даты, влияет параметр языка. Неприменимо Неприменимо sp_helplanguage sp_helplanguage [ [ = ] ‘language‘ ] Возвращает сведения о формате даты всех поддерживаемых языков. sp_helplanguage не является хранимой процедурой даты или времени. Однако на данные, выводимые функциями даты, влияет параметр языка. Неприменимо Неприменимо Функции, проверяющие значения даты и времени
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм ISDATE ISDATE ( expression ) Определяет, является ли входное выражение типа datetime или smalldatetime допустимым значением даты или времени. int Функция ISDATE детерминирована, только если используется совместно с функцией CONVERT и если заданный параметр стиля CONVERT не равен 0, 100, 9 или 109. Дата и время — см. также
Статья Описание FORMAT Возвращает значение в указанных формате и культуре (не обязательно). Для выполнения форматирования значения даты, времени и чисел с учетом локали в виде строк используется функция FORMAT. Функции CAST и CONVERT (Transact-SQL) Предоставляет сведения о преобразовании значений даты и времени в строковые литералы и обратно, а также в другие форматы даты и времени. Написание инструкций Transact-SQL, адаптированных к международному использованию Предоставляет рекомендации относительно переносимости баз данных и приложений баз данных, использующих инструкции Transact-SQL, с одного языка на другой или в многоязычную среду. Скалярные функции ODBC (Transact-SQL) Предоставляет сведения о скалярных функциях ODBC, которые могут использоваться в инструкциях Transact-SQL. К ним относятся функции даты и времени ODBC. AT TIME ZONE (Transact-SQL) Обеспечивает преобразование часовых поясов. См. также
- Функции
- Типы данных (Transact-SQL)
SET DATEFORMAT (Transact-SQL)
Задает порядок элементов даты (месяц, день, год) при интерпретации символьных строк дат. Эти строки имеют тип date, smalldatetime, datetime, datetime2 или datetimeoffset.
Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).
Синтаксис
SET DATEFORMAT
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
format | @format_var
Порядок следования составляющих частей даты. Допустимые параметры: mdy, dmy, ymd, ydm, myd и dym. Может быть задано в формате Юникод или в виде двухбайтовой кодировки (DBCS), преобразованной в Юникод. По умолчанию для английского языка США используется mdy. Значения параметра DATEFORMAT по умолчанию для всех поддерживаемых языков см. в разделе sp_helplanguage (Transact-SQL).Замечания
Значение ydm параметра DATEFORMAT не поддерживается для типов данных date, datetime2 и datetimeoffset.
Параметр DATEFORMAT может интерпретировать символьные строки по-разному для разных типов данных даты в зависимости от их формата строк. Например, интерпретации datetime и smalldatetime могут не соответствовать date, datetime2 или datetimeoffset. Этот параметр влияет на интерпретацию символьных строк при их преобразовании в значения даты для базы данных. Он не влияет на отображение значений типов данных даты, хранящихся в базе данных, а также на формат их хранения.
Некоторые форматы символьных строк, например ISO 8601, интерпретируются независимо от параметра DATEFORMAT.
Установка SET DATEFORMAT происходит во время выполнения, а не во время синтаксического анализа.
SET DATEFORMAT имеет преимущество над неявной установкой формата даты SET LANGUAGE.
Разрешения
Необходимо быть членом роли public.
Примеры
В следующем примере в качестве входных параметров сеансов используются различные строковые значения дат с одинаковым значением параметра DATEFORMAT .
-- Set date format to day/month/year. SET DATEFORMAT dmy; GO DECLARE @datevar DATETIME2 = '31/12/2008 09:01:01.1234567'; SELECT @datevar; GO -- Result: 2008-12-31 09:01:01.123 SET DATEFORMAT dmy; GO DECLARE @datevar DATETIME2 = '12/31/2008 09:01:01.1234567'; SELECT @datevar; GO -- Result: Msg 241: Conversion failed when converting date and/or time -- from character string. GO
Как изменить формат даты в SQL: подробное руководство
Как сменить формат даты в SQL? Для изменения формата даты в SQL, вы можете использовать функцию CONVERT. Вот пример:
SELECT CONVERT(varchar, your_date_column, 103) FROM your_table;
В этом примере, your_date_column — это столбец, содержащий дату, и your_table — это таблица, в которой находится этот столбец. Функция CONVERT преобразует значение даты в строку с указанным форматом. В данном случае, формат 103 обозначает дд.мм.гггг.
Детальный ответ
Как сменить формат даты в SQL
Изменение формата даты в SQL может быть необходимым при работе с базами данных. В этой статье мы рассмотрим различные способы изменения формата даты в SQL и предоставим примеры кода.
1. Функция TO_DATE()
Функция TO_DATE() позволяет изменить формат даты на заданный формат. Пример:
SELECT TO_DATE('2021-12-31', 'YYYY-MM-DD') AS new_date;
Этот запрос изменяет формат даты ‘2021-12-31′ на ’31-12-2021’.
2. Функция DATE_FORMAT()
Если вы используете MySQL, вы можете использовать функцию DATE_FORMAT() для изменения формата даты. Пример:
SELECT DATE_FORMAT('2021-12-31', '%d-%m-%Y') AS new_date;
Этот запрос также изменяет формат даты ‘2021-12-31′ на ’31-12-2021’.
3. Функция TO_CHAR()
Если вы используете Oracle, вы можете использовать функцию TO_CHAR() для изменения формата даты. Пример:
SELECT TO_CHAR(TO_DATE('2021-12-31', 'YYYY-MM-DD'), 'DD-MM-YYYY') AS new_date FROM dual;
Этот запрос также изменяет формат даты ‘2021-12-31′ на ’31-12-2021’.
4. Функция FORMAT()
В некоторых СУБД, таких как Microsoft SQL Server, доступна функция FORMAT() для изменения формата даты. Пример:
SELECT FORMAT(GETDATE(), 'dd-MM-yyyy') AS new_date;
Этот запрос изменяет формат текущей даты на ’31-12-2021′.
5. Использование разных форматирующих функций
Возможно, вам потребуется использовать различные форматирующие функции в зависимости от СУБД и ее синтаксиса. Убедитесь, что вы изучили документацию для вашей СУБД, чтобы узнать о доступных возможностях. Пример:
-- MySQL SELECT DATE_FORMAT('2021-12-31', '%d-%m-%Y') AS new_date; -- Oracle SELECT TO_CHAR(TO_DATE('2021-12-31', 'YYYY-MM-DD'), 'DD-MM-YYYY') AS new_date FROM dual; -- Microsoft SQL Server SELECT FORMAT(GETDATE(), 'dd-MM-yyyy') AS new_date;
Все эти запросы выполняют одно и то же действие — изменение формата даты.
6. Обновление данных с новым форматом даты
Чтобы обновить данные в таблице с новым форматом даты, вы можете использовать оператор UPDATE. Пример:
UPDATE table_name SET date_column = TO_DATE(date_column, 'YYYY-MM-DD');
Этот запрос обновляет столбец ‘date_column’ в таблице ‘table_name’ в соответствии с новым форматом даты. Надеюсь, что эта статья помогла вам разобраться в том, как изменить формат даты в SQL. Используйте приведенные примеры и выберите подходящий для вас метод.
Как поменять формат даты в sql
Из всех типов данных в SQL временны́е данные являются наиболее сложными . Сложность возникает по нескольким причинам, и вот некоторые из них:
- множество способов задания даты и времени
- наличие временных зон
- неочевидность вычислений некоторых значений на основании временных данных. Например, сложность вычисления возраста.
Временные данные можно получить одним из следующих способов:
- скопировать данные из существующего столбца с времéнным типом данных
- задать дату и время через строковое представление
- получить временны́е данные путём вызова встроенных функций, возвращающих временной тип данных
Для задания даты и времени используются следующие форматы:
Тип Формат по умолчанию DATE YYYY-MM-DD DATETIME YYYY-MM-DD hh:mm:ss TIMESTAMP YYYY-MM-DD hh:mm:ss TIME hhh:mm:sss YEAR YYYY — полный формат
YY или Y — сокращённый формат, который возвращает год в пределах 2000-2069 для значений 0-69 и год в пределах 1970-1999 для значений 70-99Причём, при указании даты допускается использовать любой знак пунктуации в качестве разделительного между частями разделов даты или времени. Также возможно задавать дату вообще без разделительного знака, слитно.
Примеры валидного задания временных значений через строковое представление:
MySQLSELECT CAST("2022-06-16 16:37:23" AS DATETIME) AS datetime_1, CAST("2014/02/22 16*37*22" AS DATETIME) AS datetime_2, CAST("20220616163723" AS DATETIME) AS datetime_3, CAST("2021-02-12" AS DATE) AS date_1, CAST("160:23:13" AS TIME) AS time_1, CAST("89" AS YEAR) AS year
datetime_1 datetime_2 datetime_3 date_1 time_1 year 2022-06-16T16:37:23.000Z 2014-02-22T16:37:22.000Z 2022-06-16T16:37:23.000Z 2021-02-12T00:00:00.000Z 160:23:13 1989 В запросе выше для принудительного преобразования строки в дату и время была использована функция CAST . Она необходима, если сервер не ожидает временного значения и, соответственно, автоматически не преобразует строку к нужному типу. С преобразованием типов мы более подробно познакомимся в статье «Функции преобразования типов, CAST».
Если необходимо получить временные данные из строки, которая не соответствует ни одному формату, который принимает функция CAST , то можно использовать встроенную функцию STR_TO_DATE , которая принимает произвольную строку, содержащую дату, и формат, описывающий её.
MySQLSELECT STR_TO_DATE('November 13, 1998', '%M %d, %Y') AS date;
date 1998-11-13T00:00:00.000Z Более подробное описание функции STR_TO_DATE и её аргументов можно посмотреть в справочнике.
Для генерации же текущей даты или времени нет необходимости создавать строку для последующего её преобразования в дату, потому что есть встроенные функции для получения данных значений: CURDATE , CURTIME и NOW .
MySQLSELECT CURDATE(), CURTIME(), NOW();
Иногда необходимо получить не всю дату, а только её конкретную часть, например, месяц или год.
Для этого в SQL есть следующие функции:
Функция Описание YEAR Возвращает год для указанной даты MONTH Возвращает числовое значение месяца года (от 1 до 12) даты DAY Возвращает порядковый номер дня в месяце (от 1 до 31) HOUR Возвращает значение часа (от 0 до 23) для времени MINUTE Возвращает значение минут (от 0 до 59) для времени В MySQL есть очень похожие друг на друга типы данных: DATETIME и TIMESTAMP . Они оба направлены на хранение даты и времени, но имеют ряд отличий, определяющих их целевое использование.
Критерий DATETIME TIMESTAMP Диапазон от 1000-01-01 00:00:00
до 9999-12-31 23:59:59от 1970-01-01 00:00:00
до 2038-01-19 03:14:07Часовой пояс Не учитывается
Отображается в таком виде, в котором дата была установленаУчитывается
При выборках отображается с учётом текущего часового пояса сервера БДТак как люди во всем мире хотят, чтобы полдень примерно соответствовал максимальному подъёму Солнца, то никогда не было задачи использовать универсальное время и мир был разделён на 24 часовых пояса.
В качестве точки отсчёта времени используется UTC (Coordinated Universal Time). Все остальные часовые пояса можно описать количеством часов сдвига от UTC. Для примера, часовой пояс Москвы может быть описан как UTC+3.
Часовой пояс является одной из настроек сервера баз данных и может задаваться:
- глобально
- для текущего пользователя
- для текущей пользовательской сессии
MySQLSET GLOBAL time_zone = '+03:00'; // глобально SET time_zone = '+03:00'; // для текущего пользователя SET @@session.time_zone = '+03:00'; // для текущей пользовательской сессии
Соответственно, при изменении временной зоны все значения с типом TIMESTAMP будут выводиться с учётом текущей активной временной зоны.
Хочется отдельно остановиться на наиболее популярных задачах, связанных с временным типом данных, на которых часто совершаются ошибки.
При постановке задачи найти возраст человека по дате его рождения часто возникает соблазн вычислить разницу текущего года и года рождения человека:
MySQLSELECT YEAR(NOW()) - YEAR('2003-07-03 14:10:26');
Проблема такого подхода в том, что он не учитывает был ли день рождения у данного человека в этом году или ещё нет. То есть, если на момент запроса уже наступило 3-е июля (07-03), то человек отпраздновал свой день рождения и ему уже 20 лет, иначе ему по-прежнему 19 года. Разница функций YEAR тут будет бесполезна — в обоих случаях она даст 20 лет.
Если определить возраст через разницу годов — неработающий вариант, то может возникнуть желание найти возраст через разницу дней между двумя датами, затем поделить эту разницу на количество дней в году и округлить вниз:
MySQLSELECT FLOOR(DATEDIFF(NOW(), '2003-07-03 14:10:26') / 365);
И это решение будет гораздо точнее предыдущего. Но оно не будет абсолютно точным из-за наличия високосных годов, когда в году 366 дней. Хотя погрешность в вычислении возраста для 1 человека из-за наличия високосного года достаточно низкая, в вычислениях на определение, скажем, среднего возраста среди определённого списка людей, погрешность может накапливаться и исказить реальные значения.
И как же тогда корректно определять возраст? Для этого есть готовая встроенная функция — TIMESTAMPDIFF , которая первым аргументом принимает единицу измерения, в которой нужно вернуть разницу между двумя временными значениями.
MySQLTIMESTAMPDIFF(YEAR, '2003-07-03 14:10:26', NOW());