MySQL에서 데이터를 삽입할 때 ERROR 1292 (22007): Incorrect datetime value 오류가 발생하는 경우가 많습니다. 이 오류를 피하려면 STR_TO_DATE() 함수를 사용하면 됩니다.
MySQL의 DATETIME 형식은 YYYY-MM-DD HH:MM:SS이며, 이 형식과 다른 형태의 날짜 문자열을 그대로 삽입하려고 하면 오류가 발생합니다. 아래에서 오류가 발생하는 과정과 해결 방법을 단계별로 살펴보겠습니다.
1. 오류가 발생하는 상황
먼저 테스트용 테이블을 생성합니다.
mysql> create table CorrectDatetimeDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> ArrivalTime datetime
-> );
Query OK, 0 rows affected (0.63 sec)
이제 'DD/MM/YYYY' 형식(예: 18/02/2019)으로 작성된 날짜 문자열을 그대로 삽입해 보겠습니다.
mysql> insert into CorrectDatetimeDemo(ArrivalTime) values('18/02/2019 11:15:45');
ERROR 1292 (22007): Incorrect datetime value: '18/02/2019 11:15:45' for column 'ArrivalTime' at row 1MySQL은 '18/02/2019'를 유효한 DATETIME 값으로 인식하지 못하기 때문에 위와 같은 오류가 발생합니다.
2. STR_TO_DATE() 함수로 오류 해결
해결 방법은 간단합니다. 날짜 문자열과 해당 형식을 STR_TO_DATE() 함수에 함께 전달하면, MySQL이 이를 올바른 DATETIME 값으로 변환하여 저장합니다.
기본 문법은 다음과 같습니다.
INSERT INTO yourTableName(yourDateTimeColumnName)
VALUES (STR_TO_DATE('yourDateTimeValue','%d/%m/%Y %H:%i:%s'));
여기서 %d/%m/%Y %H:%i:%s는 입력 문자열의 실제 형식을 지정하는 부분입니다. 즉, '일/월/년 시:분:초' 순서라는 의미입니다.
적용 예시
위 문법에 맞춰 다시 데이터를 삽입해 보겠습니다.
mysql> insert into CorrectDatetimeDemo(ArrivalTime) values(STR_TO_DATE('18/02/2019 11:15:45','%d/%m/%Y %H:%i:%s'));
Query OK, 1 row affected (0.21 sec)
mysql> insert into CorrectDatetimeDemo(ArrivalTime) values(STR_TO_DATE('15/01/2017 10:10:15','%d/%m/%Y %H:%i:%s'));
Query OK, 1 row affected (0.16 sec)
mysql> insert into CorrectDatetimeDemo(ArrivalTime) values(STR_TO_DATE('12/04/2016 15:30:35','%d/%m/%Y %H:%i:%s'));
Query OK, 1 row affected (0.20 sec)세 개의 INSERT문 모두 오류 없이 정상적으로 실행되었습니다.
3. 결과 확인
SELECT 문으로 테이블의 전체 데이터를 조회합니다.
mysql> select *from CorrectDatetimeDemo;
실행 결과는 다음과 같습니다.
+----+---------------------+
| Id | ArrivalTime |
+----+---------------------+
| 1 | 2019-02-18 11:15:45 |
| 2 | 2017-01-15 10:10:15 |
| 3 | 2016-04-12 15:30:35 |
+----+---------------------+
3 rows in set (0.00 sec)
정리
DATETIME 컬럼에는 반드시 YYYY-MM-DD HH:MM:SS 형식의 값이 필요합니다. 만약 외부 데이터가 'DD/MM/YYYY'처럼 다른 형식이라면, STR_TO_DATE() 함수를 사용해 형식을 명시적으로 지정한 뒤 삽입하면 'Incorrect datetime value' 오류를 손쉽게 해결할 수 있습니다.