ORDER BY วันที่ที่เก็บเป็น VARCHAR ใน MySQL ด้วย STR_TO_DATE

ปัญหา

วันที่เก็บใน VARCHAR (เช่น '01/10/2012') เรียงลำดับแบบ string จะผิด (01, 03, 04, 21, 23, 31) ต้องแปลงเป็น DATE ก่อนจึงจะเรียงถูก

วิธีแก้: ใช้ STR_TO_DATE

รูปแบบคำสั่ง

SELECT * FROM yourTableName 
ORDER BY STR_TO_DATE(yourColumnName, '%d/%m/%Y') DESC;

ตัวอย่างครบถ้วน

1. สร้างตารางเก็บวันที่เป็น VARCHAR

mysql> create table orderByDateFormatDemo 
    -> ( 
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> ArrivalDatetime varchar(100) 
    -> ); 
Query OK, 0 rows affected (0.73 sec)

2. เพิ่มข้อมูลวันที่แบบ dd/mm/yyyy

mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('01/10/2012'); 
Query OK, 1 row affected (0.20 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('03/11/2010'); 
Query OK, 1 row affected (0.15 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('04/09/2018'); 
Query OK, 1 row affected (0.14 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('31/01/2019'); 
Query OK, 1 row affected (0.17 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('21/04/2017'); 
Query OK, 1 row affected (0.21 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('23/05/2016'); 
Query OK, 1 row affected (0.12 sec)

3. ดูข้อมูลก่อนเรียง (เรียงแบบ string ผิด)

mysql> select * from orderByDateFormatDemo;

+----+-----------------+
| Id | ArrivalDatetime |
+----+-----------------+
| 1  | 01/10/2012      |
| 2  | 03/11/2010      |
| 3  | 04/09/2018      |
| 4  | 31/01/2019      |
| 5  | 21/04/2017      |
| 6  | 23/05/2016      |
+----+-----------------+

4. เรียงด้วย STR_TO_DATE (DESC = ใหม่สุดก่อน)

mysql> select * from orderByDateFormatDemo 
    -> order by STR_TO_DATE(ArrivalDatetime, '%d/%m/%Y') DESC;

+----+-----------------+
| Id | ArrivalDatetime |
+----+-----------------+
| 4  | 31/01/2019      |
| 3  | 04/09/2018      |
| 5  | 21/04/2017      |
| 6  | 23/05/2016      |
| 1  | 01/10/2012      |
| 2  | 03/11/2010      |
+----+-----------------+

Format Specifiers สำหรับ STR_TO_DATE

รูปแบบวันที่ยอดนิยม

• '%d/%m/%Y' → 31/01/2019
• '%Y-%m-%d' → 2019-01-31 (ISO)
• '%m/%d/%Y' → 01/31/2019 (US)
• '%d-%b-%Y' → 31-Jan-2019

แนะนำ: ควรเก็บวันที่ในชนิด DATE/DATETIME ตั้งแต่แรก จะได้ไม่ต้องแปลงตอน query และมี index ใช้ได้เต็มที่