Перейти к содержимому

Как преобразовать строку в дату sql

  • автор:

Преобразовать строку в дату. mySQL

Получаю котировки валют в XML. Дата котировки передается в таком формате:

Wed, 9 Oct 2019 00:00:01 GMT 

Можно как-то занести это значение в поле типа DATETIME или TIMESTAMP таблицы БД? Вроде как работает этот способ преобразования:

SELECT STR_TO_DATE('Wed, 9 Oct 2019 00:00:01 GMT','%W, %d %M %Y %H:%i:%s'); 

Но в БД занести возвращаемое значение не получается:

Ошибка SQL (1292): Truncated incorrect datetime value: 'Wed, 9 Oct 2019 00:00:01 GMT' 

Недетерминированное преобразование строк дат литералов в значения DATE

Будьте осторожны, когда разрешаете преобразование строк CHARACTER в тип данных DATE. Дело в том, что такие преобразования часто бывают недетерминированными.

Вы управляете этими недетерминированными преобразованиями, учитывая параметры SET LANGUAGE и SET DATEFORMAT.

Пример SET LANGUAGE: название месяца на польском языке

  • SET LANGUAGE Polish;

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

Например, рассмотрим слово listopad, являющееся названием месяца. Но какой это месяц, зависит от языка, который используется, по мнению системы SQL:

  • Если польский, то listopad — это 11-й месяц (ноябрь).
  • Если хорватский, то listopad — это 10-й месяц (ноябрь).
Пример кода SET LANGUAGE
--SELECT alias FROM sys.syslanguages ORDER BY alias; DECLARE @yourInputDate NVARCHAR(32) = '28 listopad 2018'; SET LANGUAGE Polish; SELECT CONVERT(DATE, @yourInputDate) AS [SL_Polish]; SET LANGUAGE Croatian; SELECT CONVERT(DATE, @yourInputDate) AS [SL_Croatian]; SET LANGUAGE English; /*** Actual output: For the two months, note the 11 versus the 10. SL_Polish 2018-11-28 SL_Croatian 2018-10-28 ***/ 

Пример SET DATEFORMAT

  • SET DATEFORMAT dmy;

Предыдущий формат ДМГ указывает, что пример строки даты «01-03-2018» будет интерпретироваться как первый день марта 2018 года.

Если указан формат МДГ, то «01-03-2018» означает третий день января 2018 года.

И если был указан ymd , нет никакой гарантии того, какие будут выходные данные. Числовое значение «2018» слишком велико, чтобы быть днем.

Конкретные страны и регионы

В Японии и Китае используется формат DATEFORMAT ГМД. Части этого формата расположены от большего к меньшему. Таким образом, этот формат хорошо сортируется. Этот формат считается международным форматом. Это международный, потому что четыре цифры года являются однозначными, и ни одна страна или регион на Земле не использует архаический формат ydm.

В других странах и регионах, таких как Германия и Франция, параметр DATEFORMAT имеет значение dmy, что означает «дд-мм-гггг». Формат dmy не сортируется правильно, но это разумная последовательность от наименьшей единицы к наибольшему.

США и Федеративные Штаты Микронезии — это единственные страны и регионы, использующие mdy, которые не сортируются. Этот формат совпадает с тем, как в английском произносятся даты.

Пример кода SET DATEFORMAT: МДГ и ДМГ

В следующем примере кода Transact-SQL используется та же строка символов даты с тремя разными параметрами DATEFORMAT. При выполнении кода получается результат, показанный в комментарии:

DECLARE @yourDateString NVARCHAR(10) = '12-09-2018'; PRINT @yourDateString + ' = the input.'; SET DATEFORMAT dmy; SELECT CONVERT(DATE, @yourDateString) AS [DMY-Interpretation-of-input-format]; SET DATEFORMAT mdy; SELECT CONVERT(DATE, @yourDateString) AS [MDY-Interpretation-of-input-format]; SET DATEFORMAT ymd; SELECT CONVERT(DATE, @yourDateString) AS [YMD-Interpretation--?--NotGuaranteed]; /*** Actual output: 12-09-2018 = the input. DMY-Interpretation-of-input-format 2018-09-12 MDY-Interpretation-of-input-format 2018-12-09 YMD-Interpretation--?--NotGuaranteed 2018-12-09 ***/ 

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

CONVERT предлагает явные коды для детерминированного управления форматами даты

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

  • CAST и CONVERT (Transact-SQL): стили даты и времени
  • CAST и CONVERT (Transact-SQL): некоторые преобразования типа данных даты и времени являются недетерминированными

Уровень совместимости 90 и выше

В SQL Server 2000 уровень совместимости был 80. Для уровней 80 и ниже неявные преобразования даты были детерминированными.

Начиная с SQL Server 2005 и уровня совместимости 90, неявные преобразования даты стали недетерминированными. Преобразования даты стали зависеть от SET LANGUAGE и SET DATEFORMAT, начиная с уровня 90.

Юникод

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

См. также раздел

  • Задание языка сеанса
  • Типы данных и функции даты и времени (Transact-SQL)
  • FORMAT (Transact-SQL)
  • ISDATE (Transact-SQL)

Как преобразовать строку в дату sql

В базе есть поле с датой (в формате 16.01.2015) типа text.
Надо при выборке преобразовать это в дату, чтобы потом отсортировать, иначе сортирует, как строку, и получается неправильно.

SELECT . (SELECT my_date FROM table_date) AS sort_date ORDER BY sort_date

В SQL не силен, не уверен, что так вообще можно.
Пробовал различные варианты (CAST, CONVERT), но ничего толкового не вышло.
При CAST(my_date as Date) преобразованная дата получается неправильная (14.02.2015 в 2014-02-20)

new player
Посмотреть профиль
Найти ещё сообщения от new player

Регистрация: 17.11.2010
Сообщений: 19,042
СУБД? Для MS SQL convert() стиль 104
https://msdn.microsoft.com/ru-ru/library/ms187928.aspx

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

Форумчанин
Регистрация: 30.08.2010
Сообщений: 153
Сообщение от Аватар
СУБД? Для MS SQL convert() стиль 104
https://msdn.microsoft.com/ru-ru/library/ms187928.aspx

MySql (для сайта)

Уже пробовал как-то так:
CONVERT(varchar(8), my_date , 104)

ошибка в синтаксисе

Последний раз редактировалось new player; 11.02.2015 в 19:26 .

new player
Посмотреть профиль
Найти ещё сообщения от new player

Регистрация: 17.11.2010
Сообщений: 19,042

Тогда с помощью SUBSTRING_INDEX и CONCAT преобразовать в ‘2015-01-16’, а потом CAST-ом в Date. Это так, навскидку, может чего и попроще есть. Чего сразу не хранить дату в родном формате?

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

Форумчанин
Регистрация: 30.08.2010
Сообщений: 153
Сообщение от Аватар

Тогда с помощью SUBSTRING_INDEX и CONCAT преобразовать в ‘2015-01-16’, а потом CAST-ом в Date. Это так, навскидку, может чего и попроще есть. Чего сразу не хранить дату в родном формате?

В итоге так получилось:

 CAST( CONCAT( SUBSTRING_INDEX(osf.shipping_date_delivery, '.', -1), '-', SUBSTRING_INDEX(SUBSTRING_INDEX(shipping_date_delivery, '.', 2), '.', -1), '-', SUBSTRING_INDEX(osf.shipping_date_delivery, '.', 1) ) as Date )

А почему не дата, не знаю — в той таблице все в text

new player
Посмотреть профиль
Найти ещё сообщения от new player

Русские Блоги

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

Например, конвертируйте «20111208» или «2011-11-8 3:43:55» в дату.

Просто используйте Convert или Cast для принудительного преобразования
Грамматика:
преобразовать (тип, который нужно преобразовать, столбец или выражение, которое нужно преобразовать)
приведение (преобразованный тип столбца или выражения AS для преобразования)

Select Convert(Varchar(10), Cast( '20070723 ' As DateTime), 120) Или Select Convert(Varchar(10), Cast( '20070723 ' As DateTime), 20) или Select Convert(Varchar(10), CONVERT(datetime,'20070723 '), 20) 

Это в основном облегчает форматирование: вам не нужны миллисекунды, у вас могут быть только часы, минуты и различные форматы.

Решение проблемы два:

select CAST('2011-11-9 9:42:05' as datetime) select CONVERT(datetime,'2011-11-9 9:42:05')

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

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