생일 데이터 집계시 이슈

부제 : 날짜와 시간 컬럼을 date 또는 datetime을 써야하는 이유.

 

연령을 계산하기 위해 생년월일이 있는 컬럼인 BirthDay nvarchar(20) 조사하였다.

 

역시나 날짜형이 아닌 문자가 들어가 있거나 월,일에 해당하는 부분에 30, 50 이런게

들어가 있거나 '098-05-01' 이런 정제되지 않은 데이터 4천여건 존재.

전체 190만건중 4천여건이라서 제외시키면 통계상으로는 큰 문제는 없지만

쿼리로 필터링은 해야 집계가 가능하다.

 

필터링하기 위해 SQLServer의 isdate()함수(리턴이 1이면 날짜형식 0이면 아님)를 사용하였으나 

isdate()함수에서는 1이 리턴되었는데도 convert(date, BirthDay)함수 사용시 에러가 발생.

isdate()결과는 1이라서 수만건중 에러나는 놈을 찾기위해 이진검색방법으로 노가다 수행.

이번 경우는 딱 한놈이 문제가 되었었는데 다른 경우도 더 있을 수 있음.

 

select isdate('000/09/23');
-----------
1
(1개 행이 영향을 받음)

select convert(date, '000/09/23');
----------
메시지 241, 수준 16, 상태 1, 줄 262
문자열을 날짜 및/또는 시간으로 변환하지 못했습니다.

 

convert()는 에러가 날 경우 null을 반환하는게 아닌 쿼리가 중단되어 버리므로

isdate()함수에서 0을 리턴시켜줘야 맞을듯한데 버그인듯하다.

 

'000'을 '00'으로 replace하면 convert될때 2000년으로 인식되긴하지만

확실하지 않은 데이터는 제외시키는게 무난하므로 not like 로 필터링해서 사용

문제는 isdate()는 1인데 convert()는 에러가 나는 다른 경우가 있을시 필터링 추가해야함.

select convert(date, birth)
  from t1
 where isdate(birth) = 1
   and birth not like '000/%';

----------------------------------------------------------------------------------------------------------

 

mysql의 경우 isdate()와 같은 함수는 필요 없는 것이

convert()와 같은 역할을 수행하는 str_to_date()가 날짜형식이 아닌경우

쿼리를 중단시키는게 아니라 null을 리턴시키므로 괜찮다.

select STR_TO_DATE('000-09-23', '%Y-%m-%d');

하지만 mysql의 경우 아래와 같이 적당히 값을 변환하기도 하고 

'000-09-23' -> '0000-09-23' : 연도를 0000년으로 변환

'00-09-23'  -> '2000-09-23' : 연도를 2000년으로 변환

'00-09-232' -> '2000-09-23' : 날짜의 232을 23으로 짤라서 사용

 

문자열이 어떤 형식인지를 str_to_date() 두번째 argument에 정의를 해줘야하므로

문자열을 먼저 같은 형식으로 변환하는 과정을 거친 후에 사용하여야한다.

 

결국 SQLServer의 isdate() 버그와 같은 불편함은 없지만 문자열의 형식이 다를 경우 더 손이 많이 갈 수 있다.

 

----------------------------------------------------------------------------------------------------------

결론

SQLServer, MYSQL 둘 다 장단점이 있을뿐 많이 번거롭고
다른 DBMS들은 또 다른 다른점들이 있으므로 원론적인 해결법은 날짜나 시간은
특별한 경우를 제외하고는 date, datetime 타입을 사용하는 것이다. 제발...

"잘 넣으면 문제 없어요"라고 varchar로 개발 된 후
잘 정제된 형태로 db에 잘 저장된 경우는 단 한번도 못 봤습니다.