home

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

MySQL แปลงรูปแบบวันที่ผิดปกติ (YYYY-DD-MM) ให้ถูกต้องด้วย STR_TO_DATE

แก้ไข Date Format ที่ผิด: STR_TO_DATE()

เมื่อข้อมูล Date เก็บในรูปแบบผิด (เช่น Year-Day-Month) ใช้ STR_TO_DATE(str, format) Parse ให้กลายเป็น DATE Type ที่ถูกต้อง

ตัวอย่าง: Format ผิด YYYY-DD-MM

CREATE TABLE DemoTable630 (ArrivalDate VARCHAR(100));

INSERT INTO DemoTable630 VALUES
('2015-21-01'), ('2018-25-12'), ('2019-15-07'), ('2016-31-03');

Query แปลง: Pattern '%Y-%d-%m' (Year-Day-Month)

SELECT STR_TO_DATE(ArrivalDate, '%Y-%d-%m') FROM DemoTable630;

ผลลัพธ์:

+-------------------------------------+
| STR_TO_DATE(ArrivalDate, '%Y-%d-%m') |
+-------------------------------------+
| 2015-01-21                          |
| 2018-12-25                          |
| 2019-07-15                          |
| 2016-03-31                          |
+-------------------------------------+

Format Specifiers สำหรับ STR_TO_DATE

Specifierความหมายตัวอย่าง Input
%Yปี 4 หลัก2024
%yปี 2 หลัก24
%mเดือน 2 หลัก (01-12)01
%cเดือน 1-2 หลัก (1-12)1
%dวัน 2 หลัก (01-31)21
%eวัน 1-2 หลัก (1-31)21
%Hชั่วโมง 24h (00-23)14
%h/%Iชั่วโมง 12h (01-12)02
%iนาที (00-59)30
%sวินาที (00-59)45
%pAM/PMPM

รูปแบบทั่วไปในไทยและการ Parse

-- 21/01/2024
STR_TO_DATE('21/01/2024', '%d/%m/%Y')

-- 21-ม.ค.-2567
STR_TO_DATE('21-ม.ค.-2567', '%d-%b-%Y')  -- ต้อง Set lc_time_names = 'th_TH'

-- 2024-01-21 14:30:45
STR_TO_DATE('2024-01-21 14:30:45', '%Y-%m-%d %H:%i:%s')

-- ISO 8601
STR_TO_DATE('2024-01-21T14:30:45Z', '%Y-%m-%dT%H:%i:%sZ')

อัปเดตข้อมูลในตาราง (Permanent Fix)

-- 1. เพิ่ม Column ใหม่ Type DATE
ALTER TABLE DemoTable630 ADD COLUMN ArrivalDateFixed DATE;

-- 2. Update ด้วย STR_TO_DATE
UPDATE DemoTable630 
SET ArrivalDateFixed = STR_TO_DATE(ArrivalDate, '%Y-%d-%m');

-- 3. ตรวจสอบ แล้ว Drop Column เก่า Rename ใหม่
ALTER TABLE DemoTable630 DROP COLUMN ArrivalDate;
ALTER TABLE DemoTable630 CHANGE ArrivalDateFixed ArrivalDate DATE;

Generated Column (MySQL 5.7+) - Auto Parse

ALTER TABLE DemoTable630
ADD COLUMN ArrivalDateParsed DATE 
GENERATED ALWAYS AS (STR_TO_DATE(ArrivalDate, '%Y-%d-%m')) STORED;

⚠️ Invalid Date Handling

-- Invalid Date เช่น '2024-31-02' (Feb 31)
SELECT STR_TO_DATE('2024-31-02', '%Y-%d-%m');  -- NULL + Warning

-- Strict Mode จะ Error ตอน Insert
SET sql_mode = 'STRICT_TRANS_TABLES';