SQL Server: Change default date time format

phunsanit

5 ปี ago

ปัญหาที่เจอประจำเวลา insert ลง database คือ การ insert วันที่ (Date) ลงไปในตารางแล้ว ข้อมูลต้นฉบับมันใช้วันที่แบบไม่ตรงกับ default date format ของ server ทำให้ไม่งงวันจากวันเป็นเดือน จากเดือนเป็นปี จากปีเป็นวัน ผิดไปไกลเลย ไม่ก็ error ถ้าข้อมูลเป็นรูปแบบเดียวกัน อย่าง 08/05/25 บางเครื่องจะเห็นเป็น เดือน วัน ปี (format อเมริกันเค้า) แต่คนไทยจะเข้าใจว่ามันคือ วันเดือนปี ถ้าไปถามคนแอฟริกาเค้าจะเข้าใจว่า ปี 08 เดือน 05 วันที่ 25 เหมือนคนครึ่งโลกด้วยซ้ำไป เราสามารถใช้ DATEFORMAT ในการบอกว่าข้อมูลที่จะกรอกมี format อะไรได้ง่าย ๆ

SET DATEFORMAT คืออะไร?

SET DATEFORMAT เป็นคำสั่งใน T-SQL (Microsoft SQL Server) ที่ใช้กำหนดว่า “จะให้ SQL Server ตีความ (Interpret) ข้อมูลตัวอักษร (String) ที่เป็นวันที่ในรูปแบบไหน”

โดยปกติ SQL Server จะไม่ได้มองวันที่เป็น ‘วันที่’ ทันที แต่มันมองเป็นข้อความ (String) ก่อน การจะแปลงข้อความนั้นลงใน field ประเภท DATE หรือ DATETIME ระบบต้องรู้ลำดับของ วัน (Day), เดือน (Month), และ ปี (Year)
รูปแบบคำสั่ง (Syntax)
SET DATEFORMAT { format | @format_var }
โดยค่า format ที่ใช้ได้แก่

mdy (Month-Day-Year) ค่า Default ส่วนใหญ่ของ US
dmy (Day-Month-Year) รูปแบบที่ประเทศไทยและยุโรปนิยมใช้
ymd (Year-Month-Day) นิยมในแถวแอฟริกา
ydm (Year-Day-Month)
myd (Month-Year-Day)
dym (Day-Year-Month)

ตัวอย่างปัญหาและการแก้ไข

สมมติเราต้องการแปลงข้อความ '31/01/2020' (31 มกราคม 2020) เป็นวันที่

เมื่อรูปแบบไม่ตรง (เกิด Error)
การใช้ SET DATEFORMAT แก้ไข
หาก Server ถูกตั้งค่าเป็น mdy (เดือนมาก่อนวัน) ระบบจะมองเลข 31 เป็น “เดือนที่ 31” ซึ่งไม่มีอยู่จริง

SET DATEFORMAT mdy; — ตั้งค่าเป็น เดือน/วัน/ปี
— บรรทัดนี้จะ Error: “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.”
SELECT CAST('31/01/2020' AS DATE);
การใช้ SET DATEFORMAT แก้ไข
เราสามารถสั่งให้ Session นี้มองรูปแบบเป็น dmy (วัน/เดือน/ปี) ก่อนที่จะประมวลผล

SET DATEFORMAT dmy; — ตั้งค่าเป็น วัน/เดือน/ปี
— บรรทัดนี้จะทำงานได้ถูกต้อง ได้ค่า: 2020-01-31
SELECT CAST('31/01/2020' AS DATE);

สิ่งที่ต้องระวัง (Important Notes)

มีผลแค่ Session ปัจจุบันเท่านั้น (Session Scope)
คำสั่ง SET DATEFORMAT จะมีผลเฉพาะกับหน้าต่าง Query หรือ Connection ที่คุณกำลังใช้งานอยู่เท่านั้น
- ถ้าคุณปิดหน้าต่างแล้วเปิดใหม่ ค่าจะถูกรีเซ็ตกลับเป็นค่า Default
- ข้อดี: ไม่กระทบกับผู้ใช้งานคนอื่นในระบบ
- ข้อเสีย: ต้องเขียนกำกับไว้เสมอใน Stored Procedure หรือ Script ที่มีความเสี่ยงเรื่องรูปแบบวันที่
ความสัมพันธ์กับ SET LANGUAGE
ค่า Default ของ DATEFORMAT จะเปลี่ยนไปตาม SET LANGUAGE ที่ตั้งไว้
- SET LANGUAGE us_english -> ค่า Default จะเป็น mdy
- SET LANGUAGE british -> ค่า Default จะเป็น dmy
  Note: การสั่ง SET DATEFORMAT จะไป Override (ทับ) ค่าของ SET LANGUAGE ในเรื่องวันที่ครับ
ไม่มีผลกับรูปแบบ ISO Standard
ถ้าคุณส่งวันที่ในรูปแบบมาตรฐาน ISO 8601 เช่น 'YYYY-MM-DD' ('2020-01-31') หรือ 'YYYYMMDD' ('20200131') SQL Server จะอ่านค่าได้ถูกต้องเสมอ ไม่ว่าคุณจะตั้ง DATEFORMAT เป็นอะไรก็ตาม

Best Practice: ควรใช้อย่างไร?

แม้ SET DATEFORMAT จะมีประโยชน์ แต่แนวทางการเขียนโค้ดที่ดีที่สุด (Best Practice) คือ

พยายามส่งค่าเป็น ISO Format เสมอ: หากเป็นไปได้ ให้เขียนวันที่ในรูปแบบ 'YYYY-MM-DD' (เช่น '2020-01-31') เพราะเป็นรูปแบบสากลที่ SQL Server เข้าใจได้ทันทีโดยไม่ต้องตั้งค่าเพิ่ม
ใช้เมื่อรับค่าจาก Legacy System: หากคุณต้อง Import ไฟล์ CSV หรือรับข้อมูลจากระบบเก่าที่ส่งวันที่มาเป็น 'DD/MM/YYYY' (เช่น '31/01/2020') การใช้ SET DATEFORMAT dmy; ไว้ที่หัวของ Script เป็นวิธีที่ปลอดภัยและง่ายที่สุด

สรุป
SET DATEFORMAT เป็นเครื่องมือเล็กๆ แต่ทรงพลังที่ช่วยขจัดความกำกวมของการแปลงวันที่ ช่วยให้ Code ของเราทำงานได้ถูกต้องไม่ว่าจะรันบน Server ที่ตั้งค่า Regional ไว้อย่างไรก็ตาม เพียงแค่ต้องจำไว้ว่า “ให้ใช้เมื่อจำเป็น และมีผลแค่ Session นั้นๆ เท่านั้น”

อ่านเพิ่มเติม