MySQL에서 VARCHAR 형태로 저장된 문자열을 실제 날짜·시간(DATETIME) 값으로 변환해야 하는 경우가 자주 있습니다. 이럴 때 STR_TO_DATE() 함수와 형식 지정자(format specifier)를 사용하면 간단하게 해결할 수 있습니다.
STR_TO_DATE() 기본 문법
select str_to_date(yourColumnName,'%d/%m/%Y %h:%i %p') as anyAliasName from yourTableName;
자주 사용되는 주요 형식 지정자는 다음과 같습니다.
- %d : 일(01~31)
- %m : 월(01~12)
- %Y : 4자리 연도
- %h : 시(01~12, 12시간제)
- %i : 분(00~59)
- %p : AM 또는 PM
예제 테이블 생성
먼저 예제로 사용할 테이블을 생성합니다.
mysql> create table DemoTable
-> (
-> DueDate varchar(100)
-> );
Query OK, 0 rows affected (0.51 sec)
insert 명령어를 사용해 레코드를 삽입합니다.
mysql> insert into DemoTable values('11/02/2019 10:35');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('21/12/2018 12:01');
Query OK, 1 row affected (0.19 sec)select 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from DemoTable;
출력 결과
+------------------+
| DueDate |
+------------------+
| 11/02/2019 10:35 |
| 21/12/2018 12:01 |
+------------------+
2 rows in set (0.00 sec)
문자열을 날짜·시간으로 변환하는 쿼리
다음은 STR_TO_DATE() 함수를 사용해 문자열을 DATETIME 형식으로 변환하는 쿼리입니다.
mysql> select str_to_date(DueDate,'%d/%m/%Y %h:%i %p') as ConvertDate from DemoTable;
출력 결과
+---------------------+
| ConvertDate |
+---------------------+
| 2019-02-11 10:35:00 |
| 2018-12-21 00:01:00 |
+---------------------+
2 rows in set (0.00 sec)
실행 결과를 보면 '11/02/2019 10:35'라는 문자열이 '2019-02-11 10:35:00' 형식의 DATETIME 값으로 성공적으로 변환된 것을 확인할 수 있습니다.
참고로 %h는 12시간제이므로, '%p'(AM/PM) 없이 '12:01'처럼 입력하면 오전 12시로 해석되어 '00:01:00'으로 변환됩니다. 오후 시간을 정확하게 변환하려면 반드시 '%p' 형식 지정자를 함께 사용하고, 24시간제가 필요한 경우에는 '%H'를 사용하는 것이 좋습니다.