데이터베이스 질문과 답변
테이블에 회원들의 생일 데이터가 19831224 이런식으로 들어가 있습니다
앞으로 10일 앞으로 20일 앞으로 200일 이런식으로 select를 해야합니다..
알려주세요..ㅠㅠ
SELECT * FROM MSMST WHERE CASE WHEN CONVERT(VARCHAR(4), GETDATE(),112)+RIGHT(BIRTH,4) <= CONVERT(VARCHAR(8), GETDATE(), 112)
WITH MSMST
AS
(
SELECT '홍길동' UserName,CAST('20131103' AS DATETIME) BIRTH UNION ALL
SELECT '홍길동1' UserName,CAST('20131113' AS DATETIME) BIRTH UNION ALL
SELECT '홍길동2' UserName,CAST('20131123' AS DATETIME) BIRTH
)
SELECT *, DATEDIFF(DAY, BIRTH, GETDATE()) DIFF
FROM MSMST
WHERE DATEDIFF(DAY, BIRTH, GETDATE()) BETWEEN 0 AND 10
THEN CONVERT(VARCHAR(8),DATEADD(YEAR, +1, GETDATE()), 112)+RIGHT(BIRTH,4)
ELSE CONVERT(VARCHAR(4), GETDATE(),112)+RIGHT(BIRTH,4) END
BETWEEN CONVERT(VARCHAR(8), GETDATE(), 112) AND CONVERT(VARCHAR, DATEADD(DAY, 20, GETDATE()) , 112 )
기존소스... 돌아가긴하나 쿼리속도가 엄청느림..
기존소스... 돌아가긴하나 쿼리속도가 엄청느림..
by 우리집아찌
[2013.10.31 18:11:14]
WITH t AS ( SELECT '19831224' BIRTH_DAY FROM DUAL UNION ALL SELECT '19831030' BIRTH_DAY FROM DUAL ) SELECT TRUNC( TO_DATE( SUBSTR(BIRTH_DAY,5,4) , 'MMDD' ) - SYSDATE) FROM t
by 우리집아찌
[2013.10.31 18:11:33]
아 MS-SQL 이시구나..
by 김성진
[2013.11.13 16:29:34]
WITH MSMST
AS
(
SELECT '홍길동' UserName,CAST('20131103' AS DATETIME) BIRTH UNION ALL
SELECT '홍길동1' UserName,CAST('20131113' AS DATETIME) BIRTH UNION ALL
SELECT '홍길동2' UserName,CAST('20131123' AS DATETIME) BIRTH
)
SELECT *, DATEDIFF(DAY, BIRTH, GETDATE()) DIFF
FROM MSMST
WHERE DATEDIFF(DAY, BIRTH, GETDATE()) BETWEEN 0 AND 10
) 버튼을 클릭하여 작성 하시면 됩니다.