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



Попробуйте наш инструмент устранения неполадок

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



ошибка преобразования даты и времени

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



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



Пример 1:

Соединенное Королевство Обозначение даты и времени отображает дату в формате день-месяц-год (10 января 2015 г. или 01.10.2015 г.), чего мы можем добиться с помощью встроенной функции SQL Server «convert» со стилем форматирования 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. Правильный формат даты с британским / французским стилем даты «дд / мм / гггг»

Объявите @date_time_value varchar (100) = '10 / 1/15 21:02:04 'выберите CONVERT (datetime2, @date_time_value, 3) как UK_Date_Time_Style

Рис. 4. Правильный формат даты с британским / французским стилем даты «дд / мм / гг»

Пример 2:

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

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

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

Объявите @date_time_value varchar (100) = '2019-02-29 21:02:04' выберите приведение (@date_time_value как datetime2) как date_time_value

Рисунок 5: Ошибка, возникшая, поскольку 2019 год не является високосным, поэтому в нем нет даты 29 февраля.

Правильный:

Объявите @date_time_value varchar (100) = '2019-02-28 21:02:04' выберите приведение (@date_time_value как datetime2) как date_time_value

Рисунок 6: Правильная дата

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

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

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

К счастью, есть одна альтернатива в международном формате даты, разработанном ISO. Формат глобального стандарта ISO 8601 «ГГГГ-ММ-ДДTчч: мм: сс» является более независимым от языка вариантом для строковых литералов и решает все эти проблемы. В то время как «гггг» - это год, «мм» - месяц, а «дд» - день. Таким образом, дата «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 или text). Всегда сохранять значение даты в столбцах типа DATE, DATETIME и предпочтительно DATETIME2 (обеспечивает большую точность) и оставлять форматирование информации о дате на уровень пользовательского интерфейса, а не извлекаются из базы данных.

2 минуты на чтение