Как да коригирам грешката на „Преобразуването е неуспешно при преобразуване на дата и / или час от символен низ“?

Има много случаи, когато датите и часовете не се показват във формата, който искате да бъде, нито изходът на заявка отговаря на нуждите на зрителите. Има няколко вградени функции на SQL Server за форматиране на низа за дата според вашите нужди, но за да бъде интерпретиран низът от SQL Server и за да се избегнат грешки при преобразуване, той трябва да бъде в правилен формат. Когато се опитваме да преобразуваме дата или час от символен низ, понякога възниква грешка. „Преобразуването не бе успешно при преобразуване на дата и / или час от символен низ.“

Фигура 1: Грешка при преобразуване на дата и / или час от символния низ

Споменатата по-горе грешка обикновено възниква, когато литералът за дата не е правилен и не може да бъде преобразуван от низа в DateTime или date. Тази грешка се дължи на редица причини, които ще обсъдим подробно заедно с набора от решения.

Пример 1:

Обединеното кралство Нотация на дата и час показва датата, използвайки формата ден-месец-година (10 януари 2015 г. или 10/1/2015), което можем да постигнем с помощта на функцията за конвертиране на SQL Server built_in функция „преобразуване“ със стил на форматиране 103.

Тук в примера по-долу можем да видим, че предоставеният низ за дата е в грешен формат. Първо, той предоставя месеца, след това дните и последната година, което е грешно и не може да бъде интерпретирано от SQL Server, което води до грешка. Правилният формат за преобразуване на дати във Великобритания, използващ стил на дата „103“, е „дд / мм / гггг“.

Грешен формат:

Декларирайте @date_time_value varchar (100) = '10 / 16/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:

Въпреки че са налични множество формати за манипулиране на стойности на датата, когато работите за глобална / международна маса, може да възникне проблем с използваемостта при избора на представяне на дата и час. Така че специфичните за културата литерали за дата / час трябва да се избягват. Ако считаме тази дата за „03/08/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' select convert (datetime2, @ date_time_value, 126) като [yyyy-mm-ddThh: mi: ss.mmm]

Фигура 7: Международен стандарт ISO 8601 формат на датата

Препоръки:

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