home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

การแปลง CHAR เป็น DATETIME ใน MySQL ด้วย STR_TO_DATE และ REPLACE

เมื่อมีข้อมูลวันที่-เวลาเก็บในคอลัมน์ CHAR/VARCHAR รูปแบบไม่มาตรฐาน (เช่น '12/31/2017 10:50' ใช้ Slash คั่น) ต้องแปลงรูปแบบก่อนจึงจะใช้ STR_TO_DATE() ได้ ใช้ REPLACE() แทนตัวคั่นให้ตรงกับ Format Specifier

ตัวอย่างการใช้งาน

สร้างตารางวันที่เป็น CHAR:

mysql> CREATE TABLE DemoTable1472 (ShippingDate CHAR(35));

เพิ่มข้อมูล (MM/DD/YYYY HH:MM):

mysql> INSERT INTO DemoTable1472 VALUES ('12/31/2017 10:50');
mysql> INSERT INTO DemoTable1472 VALUES ('01/10/2018 12:00');
mysql> INSERT INTO DemoTable1472 VALUES ('03/20/2019 09:30');

ดูข้อมูล:

mysql> SELECT * FROM DemoTable1472;
+------------------+
| ShippingDate     |
+------------------+
| 12/31/2017 10:50 |
| 01/10/2018 12:00 |
| 03/20/2019 09:30 |
+------------------+

แปลง Slash (/) เป็น Comma (,) แล้ว Parse ด้วย Format '%m,%d,%Y %T':

mysql> SELECT STR_TO_DATE(REPLACE(ShippingDate, '/', ','), '%m,%d,%Y %T') FROM DemoTable1472;

ผลลัพธ์ (DATETIME มาตรฐาน):

+----------------------------------------------------------+
| STR_TO_DATE(REPLACE(ShippingDate, '/', ','), '%m,%d,%Y %T') |
+----------------------------------------------------------+
| 2017-12-31 10:50:00                                      |
| 2018-01-10 12:00:00                                      |
| 2019-03-20 09:30:00                                      |
+----------------------------------------------------------+

คำอธิบาย Format Specifier

Specifierความหมายตัวอย่าง
%mเดือน 2 หลัก (01-12)12, 01, 03
%dวัน 2 หลัก (01-31)31, 10, 20
%Yปี 4 หลัก2017, 2018, 2019
%Tเวลา 24ชม. (HH:MM:SS)10:50:00
%,จุลภาคตรงตัว (หลัง REPLACE),

รูปแบบทั่วไปและ Format String

Input FormatREPLACESTR_TO_DATE Format
MM/DD/YYYY HH:MM:SS/ → ,'%m,%d,%Y %T'
DD-MM-YYYY- → ,'%d,%m,%Y'
YYYY.MM.DD. → ,'%Y,%m,%d'
DD Month YYYY(ไม่ต้อง Replace)'%d %M %Y'

แนวทางปฏิบัติที่ดี (Best Practice)

  1. ALTER TABLE เปลี่ยนชนิดเป็น DATETIME หลังแปลง: 
    ALTER TABLE DemoTable1472 MODIFY ShippingDate DATETIME;
  2. UPDATE ค่าจริงในตาราง (ไม่ใช่แค่ SELECT): 
    UPDATE DemoTable1472 SET ShippingDate = STR_TO_DATE(REPLACE(ShippingDate, '/', ','), '%m,%d,%Y %T');
  3. Validate หาข้อมูลที่แปลงไม่ได้ (คืน NULL): 
    SELECT * FROM DemoTable1472 WHERE STR_TO_DATE(REPLACE(ShippingDate, '/', ','), '%m,%d,%Y %T') IS NULL;

MySQL 8.0+ ทางเลือกใหม่

-- ใช้ CAST กับ ISO Format (YYYY-MM-DD HH:MM:SS) ได้เลย
SELECT CAST(REPLACE(REPLACE(ShippingDate, '/', '-'), ' ', 'T') AS DATETIME) FROM DemoTable1472;