MS-SQL 문자형 데이터를 날짜형 데이터 형식으로 변환하는 방법

* 중요한 점 : 변환하려는 모든 문자형 데이터의 형식이 같아야한다.

가끔은 문자열로 된 날짜 데이터를 날짜형 데이터로 바꾸고자 하는 경우가 있습니다. 날짜형 데이터라고 한다면 datetime 형과 smalldatetime 형을 이야기 합니다. 날짜형 데이터인 경우는 다양한 날짜관련 함수를 이용한 응용이 가능하기 때문입니다. 하지만 그렇지 않은 경우, 즉 문자형으로 되어 있는 경우 날짜 함수를 이용하기 위해서는 문자열을 날짜형으로 바꾸어주는 과정이 추가로 필요하게 됩니다.
특히 웹을 통해 입력된 날짜를 테이블에 기록하게 되는 경우 다음과 같은 형태로 날짜가 테이블에 기록이 되는 경우가 많습니다.
2003-12-01 오전 10:30:21
2003-12-02 오후 05:12:25
위와 같이 기록이 될 경우 MS SQL 서버는 데이터 중간의 '오전' 또는 '오후'를 인식히자 못하기 때문에 위 문자열을 날짜형 데이터로 변환하기가 쉽지 않습니다. 이러한 문제로 위와 같은 데이터를 정확한 날짜형 데이터로 변환하고자 하는 경우가 많이 있습니다. 이를 위한 방법을 제시하고자 합니다.
1. 에러 확인
위에서 예시한 형태의 데이터가 테이블에 기록이 된 경우 발생하는 문제를 살펴보도록 하겠습니다. 다음과 같이 만들어진 테이블이 있다고 가정을 하겠습니다.

CREATE TABLE Test ( col1 int, col2 varchar(25) ) GO

이 테이블에 다음과 같이 '오전' 또는 '오후' 가 포함된 데이터를 입력해 보겠습니다.

INSERT INTO Test VALUES(1, '2003-10-01 오후 09:10:20') INSERT INTO Test VALUES(2, '2003-10-10 오전 10:30:20') GO

이 상태에서 날짜 연산함수 중의 하나인 DateAdd() 함수를 이용해서 한달을 더한 날짜를 얻어 보도록 하겠습니다.

SELECT DateAdd(mm, 1, col2) AS col3 FROM Test

만일 정상적인 날짜형 데이터라고 한다면 위 쿼리문에 의해서 col2 컬럼에 한달을 더한 날짜가 표시되어야 하나 위 경우는 잘못된 날짜 데이터이기에 다음과 같은 에러가 발생합니다.

서버: 메시지 241, 수준 16, 상태 1, 줄 1 문자열을 datetime으로 변환하는 중 구문 오류가 발생했습니다.

우리가 보기에는 '2003-10-01 오후 09:10:20' 또는 '2003-10-10 오전 10:30:20' 데이터가 의미가 있는 날짜 데이터이지만 MS SQL 서버는 이를 날짜로 인식하지 못하기 때문에 에러가 납니다. 만일 데이터가 '2003-10-10 10:30:20 AM' 또는 '2003-10-10 19:30:20' 와 같은 형태로, 즉 '오전' 과 '오후'를 포함하지 않는다면 자동으로 형변환이 이루어져 날짜 연산이 가능하게 됩니다.
2. 정상적인 날짜형 데이터로 변환
이제 위에서 문제가 되고 있는 col2 컬럼의 값을 MS SQL 서버가 인식하는 날짜형 데이터로 바꾸는 방법을 살펴보도록 하겠습니다.
'2003-10-01 오후 09:10:20'
'2003-10-10 오전 10:30:20'
와 같은 데이터를
'2003-10-01 09:10:20 PM'
'2003-10-10 10:30:20 AM'
로 바꾸는 방법을 다음과 같이 생각할 수 있습니다.
"문자열 중간에 '오전'이 포함되어 있으면 뒷 부분에 'AM'을 붙여주고, 그렇지 않으면 뒷 부분에 'PM'을 붙여주고 중간의 '오전','오후'는 없애면 되겠네...그리고 위치를 보니 문자열 12번째 자리에서 두 글자가 '오전','오후' 값이네..."
위 방법을 그대로 SQL문으로 구현한다면 다음과 같습니다.

SELECT Substring(col2, 1, 10) + Substring(col2, 14, 9) + ' ' + CASE Substring(col2, 12, 2) WHEN '오전' THEN 'AM' ELSE 'PM' END AS col2 FROM Test

위 SQL문의 수행 결과는 다음과 같습니다.

col2 ----------------------------------------- 2003-10-01 09:10:20 PM 2003-10-10 10:30:20 AM (2개 행 적용됨)

이제 col2 컬럼의 값을 위 값으로 바꾸면 됩니다. 방법은 다음과 같이 UPDATE 문을 이용하면 됩니다.

UPDATE Test SET col2 = Substring(col2, 1, 10) + Substring(col2, 14, 9) + ' ' + CASE Substring(col2, 12, 2) WHEN '오전' THEN 'AM' ELSE 'PM' END

이제 col2 컬럼은 날짜형 데이터를 갖게 되었으므로 다음과 같은 날짜 연산에 의해 정상적인 결과 값을 보여줍니다.

SELECT DateAdd(mm, 1, col2) AS col3 FROM Test

결과는 다음과 같습니다.

col3 ------------------------------------------------------ 2003-11-01 21:10:20.000 2003-11-10 10:30:20.000 (2개 행 적용됨)

3. 컬럼의 데이터형 바꾸기
만일 col2 컬럼의 데이터형을 varchar(25) 가 아닌 datetime으로 바꾸고자 한다면 어떻게 하면 될까요?
다음과 같이 ALTER TABLE 문을 이용하면 됩니다.

ALTER TABLE Test ALTER COLUMN col2 datetime

만일 col2 컬럼에 정확한 날짜형이 아닌 데이터가 있다면 위 SQL문은 실패하게 됩니다. 만일 '2003-02-30 12:30:00 AM' 과 같은 데이터가 있었다면 위 SQL 문은 에러가 납니다. 왜나하면 2월 30일은 있을 수 없는 데이터이기 때문에 날짜형 데이터로 바꿀 수 없기 때문입니다. 컬럼의 형태가 원래부터 datetime 형 또는 smalldatetime 형이라면 이처럼 잘못된 데이터가 기록될 일이 없습니다. 문자형으로 날짜를 기록하는 경우 발생하는 문제입니다.
4. 정리
특별한 경우가 아니면 날짜 데이터는 datetime 또는 smalldatetime 형 컬럼에 기록을 하는게 좋습니다. 그렇지 않으면 날짜 연산을 사용하지 못하게 되거나, 잘못된 날짜 데이터가 기록이 될 염려가 있기 때문입니다. 특별한 경우라 함은 년별 또는 월별, 일별 검색을 위해 날짜를 분리해야 한다거나 하는 경우입니다.
데이터베이스의 무결성은 무결성을 유지하도록 제대로 설정하는것으로부터 시작됩니다