มีหลายกรณีที่วันที่และเวลาไม่ปรากฏในรูปแบบที่คุณต้องการและผลลัพธ์ของแบบสอบถามไม่ตรงกับความต้องการของผู้ดู มีคุณลักษณะในตัวของ 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' เลือกแคสต์ (@date_time_value เป็น datetime2) เป็น 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 (ให้ความแม่นยำมากขึ้น) และปล่อยให้การจัดรูปแบบข้อมูลวันที่ ไปยังเลเยอร์อินเทอร์เฟซผู้ใช้แทนการดึงข้อมูลจากฐานข้อมูล