오라클 주(week) 구하기

[ 주(Week)구하기 ] : ① ~ ⑤까지 차례대로 해본다
 
① 요일번호(1:일요일~7:토요일)
SELECT TO_NUMBER(TO_CHAR(TO_DATE(ATTN_DATE,'YYYYMMDD'),'D'))
FROM   EMPLOYEE_ATTEND
WHERE  ATTN_DATE LIKE '200612%'
       AND SNO='76990101'  
 
② 날짜+7
SELECT TO_NUMBER(TO_CHAR(TO_DATE(ATTN_DATE,'YYYYMMDD'),'DD'))+7
FROM   EMPLOYEE_ATTEND
WHERE  ATTN_DATE LIKE '200612%'
       AND SNO='76990101'     
 
③ (날짜+7)-요일번호
SELECT TO_NUMBER(TO_CHAR(TO_DATE(ATTN_DATE,'YYYYMMDD'),'DD'))+7-TO_NUMBER(TO_CHAR(TO_DATE(ATTN_DATE,'YYYYMMDD'),'D'))
FROM   EMPLOYEE_ATTEND
WHERE  ATTN_DATE LIKE '200612%'
       AND SNO='76990101'
 
④ ((날짜+7)-요일번호)/7
SELECT (TO_NUMBER(TO_CHAR(TO_DATE(ATTN_DATE,'YYYYMMDD'),'DD'))+7-TO_NUMBER(TO_CHAR(TO_DATE(ATTN_DATE,'YYYYMMDD'),'D')))/7
FROM   EMPLOYEE_ATTEND
WHERE  ATTN_DATE LIKE '200612%'
       AND SNO='76990101'
 
⑤ TRUNC(((날짜+7)-요일번호)/7)
SELECT TRUNC((TO_NUMBER(TO_CHAR(TO_DATE(ATTN_DATE,'YYYYMMDD'),'DD'))+7-TO_NUMBER(TO_CHAR(TO_DATE(ATTN_DATE,'YYYYMMDD'),'D')))/7)
FROM   EMPLOYEE_ATTEND
WHERE  ATTN_DATE LIKE '200612%'
       AND SNO='76990101'
 
→ 해당 날짜가 몇째주인지 반환(첫째주:0~)

 

 

https://softone.tistory.com/1

날짜 계산하는 오라클 함수 Oracle