MySQL에서 VARCHAR 타입으로 저장된 문자열을 날짜(DATE) 형식으로 변환해야 하는 경우가 종종 있습니다. 예를 들어 '12/1/2016'처럼 저장된 데이터를 '2016-01-12' 형태의 표준 날짜로 바꾸고 싶다면, STR_TO_DATE() 함수와 DATE_FORMAT() 함수를 함께 사용하면 됩니다.
기본 문법
VARCHAR를 날짜로 변환하는 기본 문법은 다음과 같습니다.
SELECT DATE_FORMAT(STR_TO_DATE(컬럼명, '입력형식지정자'), '출력형식지정자') AS 별칭 FROM 테이블명;
여기서 STR_TO_DATE()는 문자열을 내부적으로 날짜 값으로 변환하는 역할을 하고, 이어서 DATE_FORMAT()이 원하는 출력 형식으로 다시 포맷팅합니다.
예제 테이블 생성
문법을 실제로 이해하기 위해 먼저 테스트용 테이블을 만들어 보겠습니다.
mysql> create table VarcharToDate
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Created_Time varchar(100),
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (1.10 sec)샘플 데이터 입력
INSERT 명령으로 여러 개의 레코드를 추가합니다. 날짜가 '일/월/년' 형식의 문자열로 저장되어 있는 점에 주목하세요.
mysql> insert into VarcharToDate(Created_Time) values('12/1/2016');
Query OK, 1 row affected (0.14 sec)
mysql> insert into VarcharToDate(Created_Time) values('14/3/2017');
Query OK, 1 row affected (0.18 sec)
mysql> insert into VarcharToDate(Created_Time) values('15/3/2018');
Query OK, 1 row affected (0.21 sec)
mysql> insert into VarcharToDate(Created_Time) values('19/5/2011');
Query OK, 1 row affected (0.18 sec)
mysql> insert into VarcharToDate(Created_Time) values('19/8/2019');
Query OK, 1 row affected (0.15 sec)
mysql> insert into VarcharToDate(Created_Time) values('21/11/2020');
Query OK, 1 row affected (0.23 sec)저장된 데이터 확인
SELECT 문으로 테이블의 전체 레코드를 조회해 보겠습니다.
mysql> select *from VarcharToDate; +----+--------------+ | Id | Created_Time | +----+--------------+ | 1 | 12/1/2016 | | 2 | 14/3/2017 | | 3 | 15/3/2018 | | 4 | 19/5/2011 | | 5 | 19/8/2019 | | 6 | 21/11/2020 | +----+--------------+ 6 rows in set (0.00 sec)
VARCHAR를 날짜로 변환하는 쿼리
이제 본격적으로 변환 작업을 진행합니다. 변환은 두 단계로 이루어집니다. 첫 번째 단계에서는 STR_TO_DATE() 함수를 사용해 문자열을 날짜로 변환하고, 두 번째 단계에서는 DATE_FORMAT() 함수로 원하는 최종 날짜 형식을 지정합니다.
mysql> select date_format(str_to_date(Created_Time, '%d/%m/%Y'), '%Y-%m-%d') as Date from VarcharToDate;
위 쿼리에서 사용된 형식 지정자의 의미는 다음과 같습니다.
- %d : 일(day), 두 자리 숫자 (01~31)
- %m : 월(month), 두 자리 숫자 (01~12)
- %Y : 연도(year), 네 자리 숫자
실행 결과
쿼리 실행 결과는 다음과 같습니다. '일/월/년' 형식의 문자열이 표준 ISO 형식인 '년-월-일'로 깔끔하게 변환된 것을 확인할 수 있습니다.
+------------+ | Date | +------------+ | 2016-01-12 | | 2017-03-14 | | 2018-03-15 | | 2011-05-19 | | 2019-08-19 | | 2020-11-21 | +------------+ 6 rows in set (0.00 sec)
마무리 및 참고 사항
이처럼 MySQL에서는 STR_TO_DATE()와 DATE_FORMAT()을 조합하면 다양한 형식의 문자열 날짜를 손쉽게 변환할 수 있습니다. 만약 결과 값을 단순히 화면에 표시하는 용도가 아니라 데이터 타입 자체를 영구적으로 변경하고 싶다면, ALTER TABLE ... MODIFY 구문을 사용해 컬럼 타입을 DATE로 변경하는 방법도 고려해 보세요. 다만 컬럼 변경 시 기존 문자열이 유효한 날짜 형식이어야 하므로, 변환 전 데이터 검증을 먼저 수행하는 것이 안전합니다.