Как исправить ошибку «Ошибка преобразования при преобразовании даты и/или времени из символьной строки»?

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

Рис. 1. Ошибка преобразования даты и/или времени из строки символов

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

Пример 1:

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

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

Неверный формат:

Объявить @date_time_value varchar(100)= ’16/10/2015 21:02:04′, выбрать CONVERT(datetime2, @date_time_value, 103) как UK_Date_Time_StyleРис. 2. Неправильный формат даты, приводящий к ошибке

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

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

Объявите @date_time_value varchar(100)= ’10/1/15 21:02:04′ выберите CONVERT(datetime2, @date_time_value, 103) как Date_Time_StyleРис. 3. Правильный формат даты с британским/французским стилем даты «дд/мм/гггг».Рис. 4. Правильный формат даты с британским/французским стилем даты «дд/мм/гг»

Пример 2:

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

Неверная дата:

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

Объявите @date_time_value varchar(100)= ‘2019-02-29 21:02:04’ выберите приведение(@date_time_value as datetime2) как date_time_valueРис. 5. Возникла ошибка, поскольку 2019 год не является високосным, поэтому в нем нет даты 29 февраля.

Правильный:

Объявите @date_time_value varchar(100)= ‘2019-02-28 21:02:04’ выберите приведение(@date_time_value as datetime2) как date_time_valueРисунок 6: Правильная дата

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

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

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

К счастью, есть одна альтернатива международному формату даты, разработанному ISO. Формат глобального стандарта ISO 8601 «ГГГГ-ММ-ДДТчч: мм: сс» является более независимым от языка вариантом для строковых литералов и решает все эти проблемы. Принимая во внимание, что «гггг» — это год, «мм» — месяц, а «дд» — день. Таким образом, дата «8 марта 2018 года» в международном формате ISO записывается как «2018-03-08». Таким образом, формат ISO является лучшим выбором для представления даты.

Объявите @date_time_value varchar(100)= ‘2019-03-28 21:02:04’, выберите convert(datetime2,@date_time_value,126) как [yyyy-mm-ddThh:mi:ss.mmm]Рис. 7. Формат даты международного стандарта ISO 8601.

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

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