จะแก้ไขข้อผิดพลาด "การแปลงล้มเหลวเมื่อแปลงวันที่และ / หรือเวลาจากสตริงอักขระ" ได้อย่างไร

มีหลายกรณีที่วันที่และเวลาไม่ปรากฏในรูปแบบที่คุณต้องการและผลลัพธ์ของแบบสอบถามไม่ตรงกับความต้องการของผู้ดู มีคุณลักษณะในตัวของ SQL Server หลายประการในการจัดรูปแบบสตริงวันที่ตามความต้องการของคุณ แต่สำหรับสตริงที่จะตีความโดย SQL Server และเพื่อหลีกเลี่ยงข้อผิดพลาดในการแปลงควรอยู่ในรูปแบบที่เหมาะสม เมื่อเราพยายามแปลงวันที่หรือเวลาจากสตริงอักขระตามข้อผิดพลาดบางครั้งเกิดขึ้น “ การแปลงล้มเหลวเมื่อแปลงวันที่และ / หรือเวลาจากสตริงอักขระ”

ข้อผิดพลาดที่กล่าวถึงข้างต้นมักเกิดขึ้นเมื่อลิเทอรัลวันที่ไม่ถูกต้องและไม่สามารถแปลงจากสตริงเป็น DateTime หรือวันที่ได้ ข้อผิดพลาดนี้เกิดจากสาเหตุหลายประการซึ่งเราจะพูดถึงในรายละเอียดพร้อมกับชุดวิธีแก้ปัญหา

ตัวอย่างที่ 1:

สัญกรณ์วันที่และเวลาของสหราชอาณาจักรแสดงวันที่โดยใช้รูปแบบวันเดือนปี (10 มกราคม 2015 หรือ 10/1/2015) ซึ่งเราสามารถทำได้โดยใช้ฟังก์ชัน "แปลง" ในตัวของเซิร์ฟเวอร์ SQL ที่มีรูปแบบการจัดรูปแบบ 103

ในตัวอย่างด้านล่างนี้เราจะเห็นว่าสตริงวันที่ที่ระบุอยู่ในรูปแบบที่ไม่ถูกต้อง ประการแรกเป็นการระบุเดือนจากนั้นวันและปีที่แล้วซึ่งผิดพลาดและไม่สามารถตีความได้โดย SQL Server ซึ่งส่งผลให้เกิดข้อผิดพลาด รูปแบบที่ถูกต้องสำหรับการแปลงวันที่ของรูปแบบสหราชอาณาจักรโดยใช้รูปแบบวันที่“ 103” คือ“ dd / mm / yyyy”

รูปแบบผิด:

ประกาศ @date_time_value varchar (100) = '10 / 16/2015 21:02:04 'เลือก CONVERT (datetime2, @date_time_value, 103) เป็น UK_Date_Time_Style

รูปแบบที่ถูกต้อง:

รูปแบบวันที่ของอังกฤษและฝรั่งเศสคือ 103 =“ dd / mm / yyyy” หรือ 3 =” dd / mm / yy” ที่นี่ 103 และ 3 คือรูปแบบวันที่

ประกาศ @date_time_value varchar (100) = '10 / 1/15 21:02:04 'เลือก CONVERT (datetime2, @date_time_value, 103) เป็น Date_Time_Style

ประกาศ @date_time_value varchar (100) = '10 / 1/15 21:02:04 'เลือก CONVERT (datetime2, @date_time_value, 3) เป็น 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”
• ในสไตล์ยุโรปตีความว่า“ วันที่ 3 สิงหาคม 2018”

โชคดีที่มีทางเลือกหนึ่งในรูปแบบวันที่สากลที่พัฒนาโดย ISO รูปแบบ ISO 8601 มาตรฐานสากล“ YYYY-MM-DDThh: mm: ss” เป็นตัวเลือกที่ไม่ขึ้นกับภาษาสำหรับตัวอักษรสตริงและจะแก้ไขปัญหาเหล่านี้ ในขณะที่“ ปปปป” คือปี“ mm” คือเดือนและ“ dd” คือวัน ดังนั้นวันที่“ 8 มีนาคม 2018” ในรูปแบบ ISO สากลจึงเขียนว่า“ 2018-03-08” ดังนั้นรูปแบบ ISO จึงเป็นตัวเลือกที่ดีที่สุดสำหรับการแสดงวันที่

ประกาศ @date_time_value varchar (100) = '2019-03-28 21:02:04' เลือกแปลง (datetime2, @ date_time_value, 126) เป็น [yyyy-mm-ddThh: mi: ss.mmm]

คำแนะนำ:

หวังว่าบทความนี้จะช่วยคลายความสับสนที่ฉันพบบ่อยในชุมชนเกี่ยวกับค่าวันที่ / เวลา อย่างไรก็ตามขอแนะนำว่าอย่าจัดเก็บวันที่ในประเภทข้อความ (varchar, char, nvarchar, nchar หรือ text) เก็บค่าวันที่ไว้ใน DATE, DATETIME เสมอและควรจัดประเภทคอลัมน์ DATETIME2 (ให้ความแม่นยำมากกว่า) และปล่อยให้การจัดรูปแบบข้อมูลวันที่ ไปยังเลเยอร์อินเทอร์เฟซผู้ใช้แทนการดึงข้อมูลจากฐานข้อมูล