Преобразовать строку в дату. 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')