문자열로 저장된 날짜 데이터를 MySQL의 날짜(DATETIME) 형식으로 올바르게 변환하려면 STR_TO_DATE() 함수를 사용하면 됩니다. 이 함수는 지정된 형식 문자열에 따라 텍스트를 해석하여 실제 날짜·시간 값으로 반환해 줍니다.
먼저 예제에 사용할 테이블을 생성해 보겠습니다.
mysql> create table DemoTable
-> (
-> DueDatetime varchar(100)
-> );
Query OK, 0 rows affected (1.03 sec)
다음은 insert 명령을 사용하여 테이블에 샘플 레코드를 삽입하는 과정입니다. 여기서는 '일-월-년 시:분' 형태의 문자열을 저장합니다.
mysql> insert into DemoTable values('22-06-2019 14:40');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('02-07-2015 13:10');
Query OK, 1 row affected (0.18 sec)select 문을 사용하여 테이블에 저장된 모든 레코드를 확인해 보겠습니다.
mysql> select *from DemoTable;
출력 결과
위 쿼리는 다음과 같은 결과를 생성합니다.
+------------------+
| DueDatetime |
+------------------+
| 22-06-2019 14:40 |
| 02-07-2015 13:10 |
+------------------+
2 rows in set (0.00 sec)
현재 날짜가 varchar(문자열) 타입으로 저장되어 있기 때문에, 날짜 연산이나 정렬을 수행하기 어렵습니다. 이제 STR_TO_DATE() 함수를 사용하여 이를 진짜 날짜 값으로 변환하는 쿼리를 살펴보겠습니다.
mysql> select str_to_date(DueDatetime,'%d-%m-%Y %H:%i') from DemoTable;
출력 결과
위 쿼리는 다음과 같은 결과를 생성합니다.
+-------------------------------------------+
| str_to_date(DueDatetime,'%d-%m-%Y %H:%i') |
+-------------------------------------------+
| 2019-06-22 14:40:00 |
| 2015-07-02 13:10:00 |
+-------------------------------------------+
2 rows in set (0.00 sec)
형식 지정자(Format Specifier) 정리
STR_TO_DATE()의 두 번째 인자는 입력 문자열의 구조를 알려주는 형식 문자열입니다. 위 예제에서 사용된 주요 지정자는 다음과 같습니다.
%d : 일(01~31)
%m : 월(01~12)
%Y : 4자리 연도(예: 2019)
%H : 시(00~23, 24시간 표기)
%i : 분(00~59)
즉, '%d-%m-%Y %H:%i'는 '22-06-2019 14:40'처럼 일-월-연도 시:분 순서로 된 문자열을 의미하며, MySQL은 이를 표준 DATETIME 형식인 '2019-06-22 14:40:00'으로 변환합니다.
참고 사항
변환된 값을 영구적으로 저장하고 싶다면 ALTER TABLE로 컬럼 타입을 DATETIME으로 변경하거나, UPDATE 문과 함께 STR_TO_DATE()를 사용해 기존 데이터를 갱신할 수 있습니다. 또한 입력 문자열의 실제 순서와 형식 지정자가 일치하지 않으면 NULL이 반환되므로, 데이터 형식을 반드시 먼저 확인하는 것이 좋습니다.