Как исправить ошибку ‘Conversion Failed when Converting Date and/or Time from Character String’ Error?

Содержание:

• Пример 1:
• Неправильный формат:
• Правильный формат:
• Пример 2:
• Неправильная дата:
• Правильный вариант:
• Формат даты ISO 8601:
• Рекомендации:

Существует множество случаев, когда даты и время отображаются не в том формате, в котором вы хотите, а вывод запроса не соответствует потребностям зрителей. Существует несколько встроенных функций SQL Server’ для форматирования строки даты в соответствии с вашими потребностями, но для того, чтобы строка была интерпретирована SQL Server и чтобы избежать ошибок преобразования, она должна иметь правильный формат. Когда мы пытаемся преобразовать дату или время из символьной строки, иногда возникает следующая ошибка. “При преобразовании даты и/или времени из символьной строки произошел сбой конвертации.”

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

Пример 1:

Нотация даты и времени Соединенного Королевства отображает дату в формате день-месяц-год (10 января 2015 года или 10/1/2015), чего мы можем добиться с помощью встроенной в SQL Server функции «convert» со стилем форматирования 103.

В приведенном ниже примере мы видим, что предоставленная строка даты имеет неправильный формат. Сначала указывается месяц, затем дни и, наконец, год, что является неправильным и не может быть интерпретировано SQL Server, что приводит к ошибке. Правильным форматом для преобразования даты в стиле Великобритании с использованием стиля даты «103» является «dd/mm/yyyy».

Неправильный формат:

Объявить @date_time_value varchar(100)= '10/16/2015 21:02:04'
select CONVERT(datetime2, @date_time_value, 103) as UK_Date_Time_Style

Правильный формат:

Британский и французский формат даты — 103 = «dd/mm/yyy» или 3=» dd/mm/yy». Здесь 103 и 3 — это стили даты.

Объявить @date_time_value varchar(100)= '10/1/15 21:02:04'
select CONVERT(datetime2, @date_time_value, 103) as Date_Time_Style

Объявить @date_time_value varchar(100)= '10/1/15 21:02:04'
select CONVERT(datetime2, @date_time_value, 3) as UK_Date_Time_Style

Пример 2:

Иногда преобразование строки в дату в SQL-сервере приводит к ошибке, но не из-за используемых форматов даты или времени, а потому что вы’пытаетесь сохранить неверную информацию, которая’неприемлема для схемы.

Неправильная дата:

Причина следующей ошибки заключается в том, что в 2019 году не существует такой даты, как «29 февраля», поскольку этот год не является високосным.

Объявить @date_time_value varchar(100)= '2019-02-29 21:02:04'
select cast(@date_time_value as datetime2) as date_time_value

Правильный вариант:

Объявление @date_time_value varchar(100)= '2019-02-28 21:02:04'
select cast(@date_time_value as datetime2) as date_time_value

Формат даты ISO 8601:

Хотя для манипулирования значениями даты доступны многочисленные форматы, при работе с глобальной/международной массой выбор представления даты во времени может стать проблемой юзабилити. Поэтому следует избегать культурно-специфических литералов даты/времени. Если считать эту дату «03/08/2018», то в разных регионах мира она будет интерпретирована по-разному.

• В британском стиле это интерпретируется как «8 марта 2018 года»
• В европейском стиле это интерпретируется как «3rd of August 2018»

К счастью, в международном формате даты, разработанном ISO, есть альтернатива. Глобальный стандарт ISO 8601 формат “YYYY-MM-DDThh: mm: ss” является более независимым от языка вариантом для строковых литералов и решает все эти проблемы. Где «yyyy» — это год, «mm» — месяц и «dd» — день. Таким образом, дата «8 марта 2018 года» в международном формате ISO записывается как «2018-03-08». Таким образом, формат ISO является лучшим выбором для представления даты.

Объявить @date_time_value varchar(100)= '2019-03-28 21:02:04'
select convert(datetime2,@date_time_value,126) as [yyyy-mm-ddThh:mi:ss.ммм]

Рекомендации:

Надеюсь, эта статья поможет устранить путаницу, которую я часто наблюдаю в сообществе в отношении значений даты/времени. Однако рекомендуется никогда не хранить даты в столбцах текстового типа (varchar, char, nvarchar, nchar или text). Всегда храните значение даты в столбцах типа DATE, DATETIME и предпочтительно DATETIME2 (обеспечивает большую точность), а также оставьте форматирование информации о дате на уровне пользовательского интерфейса, а не извлекайте ее из базы данных.