꿈꾸는 개발자, DBA 커뮤니티 구루비

생일자 조회질문...

by 레알 [SQL Query] [2016.01.20 20:19:55]

안녕하세요

생일자를 조회 하려고하는데 잘안되네여...

DB는 오라클이고 BIRTH_DAY 컬럼은 VARCHAR2 8 BYTE 인니다.

100일 이전생일과 100일이후 생일인 사람을 구하려는데 전후 15 일까지만 검색되고 100일 전후로하면 안되네요...

해결방법없을까여?

SELECT *
FROM BIRTH
WHERE 1 = 1
AND SUBSTR (BIRTH_DAY, 5, 4) BETWEEN TO_CHAR (SYSDATE - 100, 'MMDD')

AND TO_CHAR (SYSDATE + 100, 'MMDD')

by jkson [2016.01.21 08:12:35]

현재 기준 TO_CHAR (SYSDATE - 100, 'MMDD')는 1013

TO_CHAR (SYSDATE + 100, 'MMDD')는 0430 이니

1013보다 크고 0430보다 작은 데이터는 없는 거죠ㅋ

15일일 경우는 0106, 0205로 조건이 만족 되는 거구요

연도 조건까지 붙여서 하셔야 할 거 같아요.

월만 가지고 판단할 때 작년생일, 올해생일, 내년생일이 100일 안인 데이터면 맞을 거 같네요.


SELECT *
  FROM BIRTH
 WHERE TO_CHAR (SYSDATE, 'YYYY') || SUBSTR (BIRTH_DAY, 5) 
       BETWEEN TO_CHAR (SYSDATE - 100, 'YYYYMMDD') AND TO_CHAR (SYSDATE + 100, 'YYYYMMDD')
    OR TO_CHAR (SYSDATE - 366, 'YYYY') || SUBSTR (BIRTH_DAY, 5) 
       BETWEEN TO_CHAR (SYSDATE - 100, 'YYYYMMDD') AND TO_CHAR (SYSDATE + 100, 'YYYYMMDD')
    OR TO_CHAR (SYSDATE + 366, 'YYYY') || SUBSTR (BIRTH_DAY, 5)
       BETWEEN TO_CHAR (SYSDATE - 100, 'YYYYMMDD') AND TO_CHAR (SYSDATE + 100, 'YYYYMMDD')

*창조님 말씀 듣고 366으로 변경

by 창조의날개 [2016.01.21 09:27:18]

TO_CHAR (BIRTH_DAY, 'MMDD')는 SUBSTR (BIRTH_DAY, 5)로 바꿔야 할 거 같구요..

SYSDATE - 365는 ADD_MONTHS(SYSDATE,-12)로 바꿔야 윤년에도 된다고 합니다..

그리고 가능하면 좌변을 변형하지 않는 방법을 만들어 보는게 좋을거 같은데요...

BIRTH_DAY에 인덱스걸려면...

근데 좀 어렵네요.. ^^;;

by jkson [2016.01.21 10:20:16]

birth_day 컬럼이 varchar 형이군요ㅋ add_months 쓰려다가 간단히 하려고 그냥 - 365 했는데 윤년은 생각을 못했네요. add_months를 하든지 -366 하든지 하면 될 것 같네요~

변형은 안해주는 게 최대한 좋을 것 같긴한데 보통 생일 같은 경우는 인덱스를 만들지 않을 것 같아서요.

만약 인덱스가 있다고 하더라도 변형하지 않고 만들려면 좀 복잡하겠네요.

by 마농 [2016.01.21 10:40:10]

WITH birth AS
(
SELECT '19881012' birth_day FROM dual
UNION ALL SELECT '19941013' FROM dual
UNION ALL SELECT '19970304' FROM dual
)
SELECT *
  FROM birth
     , (SELECT TO_CHAR(sysdate - 100, 'mmdd') s
             , TO_CHAR(sysdate + 100, 'mmdd') e
          FROM dual
        )
 WHERE (s <= e AND SUBSTR(birth_day, 5) BETWEEN s AND e )
    OR (s > e AND (SUBSTR(birth_day, 5) >= s OR SUBSTR(birth_day, 5) <= e))
;

by 창조의날개 [2016.01.21 11:17:18]


SELECT BIRTH_DAY
FROM BIRTH
WHERE (BIRTH_DAY BETWEEN TO_CHAR(TO_DATE(SUBSTR(BIRTH_DAY,1,4)||DECODE(TO_CHAR(SYSDATE,'MMDD'),'0229','0228',TO_CHAR(SYSDATE,'MMDD')),'YYYYMMDD')-100,'YYYYMMDD')
                     AND TO_CHAR(TO_DATE(SUBSTR(BIRTH_DAY,1,4)||DECODE(TO_CHAR(SYSDATE,'MMDD'),'0229','0228',TO_CHAR(SYSDATE,'MMDD')),'YYYYMMDD')+100,'YYYYMMDD'))
   OR (BIRTH_DAY BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(BIRTH_DAY,1,4)||DECODE(TO_CHAR(SYSDATE,'MMDD'),'0229','0228',TO_CHAR(SYSDATE,'MMDD')),'YYYYMMDD')-100,-12),'YYYYMMDD')
                     AND TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(BIRTH_DAY,1,4)||DECODE(TO_CHAR(SYSDATE,'MMDD'),'0229','0228',TO_CHAR(SYSDATE,'MMDD')),'YYYYMMDD')+100,-12),'YYYYMMDD'))
   OR (BIRTH_DAY BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(BIRTH_DAY,1,4)||DECODE(TO_CHAR(SYSDATE,'MMDD'),'0229','0228',TO_CHAR(SYSDATE,'MMDD')),'YYYYMMDD')-100,+12),'YYYYMMDD')
                     AND TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(BIRTH_DAY,1,4)||DECODE(TO_CHAR(SYSDATE,'MMDD'),'0229','0228',TO_CHAR(SYSDATE,'MMDD')),'YYYYMMDD')+100,+12),'YYYYMMDD'))
;

이렇게 하면 BIRTH_DAY 로 인덱스 만들면 INDEX FULL SCAN 할거 같은데...

by 마농 [2016.01.21 11:37:42]

TO_DATE(출생녕도 + 현재월일) 하셨네요.
만약 현재월일이 0229 인데 출생년도엔 '0209' 가 없었다면? 에러발생하겠네요.

by 창조의날개 [2016.01.21 13:19:39]

역시 예리 하십니다..

윤년만 생각하고 윤달을 생각못했네요..

by 레알 [2016.01.21 18:20:33]

소중한 답변 감사드립니다~

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변