Как поменять формат даты в sql
Перейти к содержимому

Как поменять формат даты в sql

  • автор:

Типы данных и функции даты и времени (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

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

    Примеры валидного задания временных значений через строковое представление:

    MySQL
    SELECT 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 , которая принимает произвольную строку, содержащую дату, и формат, описывающий её.

    MySQL
    SELECT STR_TO_DATE('November 13, 1998', '%M %d, %Y') AS date; 
    date
    1998-11-13T00:00:00.000Z

    Более подробное описание функции STR_TO_DATE и её аргументов можно посмотреть в справочнике.

    Для генерации же текущей даты или времени нет необходимости создавать строку для последующего её преобразования в дату, потому что есть встроенные функции для получения данных значений: CURDATE , CURTIME и NOW .

    MySQL
    SELECT 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.

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

    • глобально
    • для текущего пользователя
    • для текущей пользовательской сессии
    MySQL
    SET GLOBAL time_zone = '+03:00'; // глобально SET time_zone = '+03:00'; // для текущего пользователя SET @@session.time_zone = '+03:00'; // для текущей пользовательской сессии 

    Соответственно, при изменении временной зоны все значения с типом TIMESTAMP будут выводиться с учётом текущей активной временной зоны.

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

    При постановке задачи найти возраст человека по дате его рождения часто возникает соблазн �� вычислить разницу текущего года и года рождения человека:

    MySQL
    SELECT YEAR(NOW()) - YEAR('2003-07-03 14:10:26'); 

    Проблема такого подхода в том, что он не учитывает был ли день рождения у данного человека в этом году или ещё нет. То есть, если на момент запроса уже наступило 3-е июля (07-03), то человек отпраздновал свой день рождения и ему уже 20 лет, иначе ему по-прежнему 19 года. Разница функций YEAR тут будет бесполезна — в обоих случаях она даст 20 лет.

    Если определить возраст через разницу годов — неработающий вариант, то может возникнуть желание найти возраст через разницу дней между двумя датами, затем поделить эту разницу на количество дней в году и округлить вниз:

    MySQL
    SELECT FLOOR(DATEDIFF(NOW(), '2003-07-03 14:10:26') / 365); 

    И это решение будет гораздо точнее предыдущего. Но оно не будет абсолютно точным из-за наличия високосных годов, когда в году 366 дней. Хотя погрешность в вычислении возраста для 1 человека из-за наличия високосного года достаточно низкая, в вычислениях на определение, скажем, среднего возраста среди определённого списка людей, погрешность может накапливаться и исказить реальные значения.

    И как же тогда корректно определять возраст? Для этого есть готовая встроенная функция — TIMESTAMPDIFF , которая первым аргументом принимает единицу измерения, в которой нужно вернуть разницу между двумя временными значениями.

    
    

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *