11.변환함수(2)

1. to_number : 문자 -> 숫자
 문자 -> 숫자
 문자를 숫자와 연산 또는 비교하기 위해 숫자 타입으로 변경할 때 주로 사용
 묵시적 형변환에 의해 자주 발생

ex) where to_char(hiredate, 'mm') = 1 => where to_number(to_char(hiredate, 'mm')) = 1
   ** 문자와 숫자 비교시 문자를 숫자로 변환하여 비교

 

 

2. to_date // 해석을 하기 위한 용도 // 인식시키는게 목적

1) 문자 -> 날짜 : 날짜처럼 생긴 문자를 날짜타입으로 인식시키기 위해 사용(날짜파싱)


2) 숫자 -> 날짜 : 날짜처럼 생긴 숫자를 날짜타입으로 인식시키기 위해 사용(날짜파싱)


** 사용법
to_date(문자 또는 숫자, 포맷)
- 포맷은 날짜를 해석하기 위한 용도로 전달
- 포맷 생략 가능(DBMS의 기본 날짜 포맷 전달 시)


예) '2024/09/26' 100일 뒤 날짜 출력

select '2024/09/26' + 100 from dual;


select to_date('2024/09/26','YYYY/MM/DD')+100 
  from dual;

예) to_date에서의 포맷의 의미
select to_date('2024/09/26') from dual;
select to_date('09/26/2024') from dual;
select to_date('09/26/2024','MM/DD/YYYY') 
  from dual;

 

순서대로의 결과를 알 수 있다. 

 

 

 

 

 

select to_char(to_date('09/26/2024','MM/DD/YYYY') +100,'MM/DD/YYYY')
  from dual;

예제) 보너스를 2020년 10월 1일에 부여,
    보너스는 보너스 부여 날짜 기준으로 근속연수*기본급여(sal)*0.1
    직원의 급여와 보너스를 이름과 함께 출력
select * 
  from emp;

select ename, sal, hiredate,
       trunc(months_between(to_date('2020/10/01','YYYY/MM/DD'),hiredate)/12) as 근속연수,
       trunc(months_between(to_date('2020/10/01','YYYY/MM/DD'),hiredate)/12)*sal*0.1 as 보너스
  from emp;
 

 

 
예제) student 테이블을 사용하여 각 학생의 이름, 학년, 생년월을 출력
     단, 생년월일은 jumin 컬럼을 사용하여 날짜형태로 출력하되 아래와 같은 형태로 표현
     10-24,1990

select *
  from student;
 

sol1) 
select name 이름, grade 학년,
       to_char(to_date(substr(jumin,1,6),'RR/MM/DD'),'MM-DD,YYYY') as 생년월일
  from student;
 

 

 

sol2)   
select name, jumin, birthday,
       to_date(substr(jumin,1,6)),
       to_date(substr(jumin,1,6),'YYMMDD'),
       to_date(substr(jumin,1,6),'RRMMDD')
  from student;
  

** 두자리 연도에 대한 해석 : YY, RR
1) YY : y2k 이후 연도 해석
2) RR : y2k 이전/이후 연도 해석
        ~ 49 : 2000년대 해석
        50 ~ : 1900년대 해석 
        
select to_date('10/12/25','YY/MM/DD'),
       to_date('99/12/25','YY/MM/DD'),
       to_date('10/12/25','RR/MM/DD'),
       to_date('99/12/25','RR/MM/DD')
  from dual;
 

  

** DBMS 설정 날짜 파라미터 확인
select * 
  from nls_session_parameters
 where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); 
 
   

** 세선의 날짜 포맷 변경
예제) 오늘날짜에서 "일" 추출

select sysdate,
       substr(sysdate,9,2) as 일,
       to_char(sysdate,'dd') as 일2,
       extract(day from sysdate) as 일3
  from dual;
  
substr으로 날짜 데이터에 날짜의 일부 추출 시 정확한 추출이 되지 않는 경우는
눈에 보이는 날짜의 형식(orange 같은 툴에 설정된 날짜 포맷에 맞게 출력)과 DBMS의 기본 날짜 포맷이 서로 달라서이다. 
즉, 눈에 보이는대로 날짜를 추출하면 원본은 그와 다른 포맷일 수 있으므로 항상 같은 결과가 나오지 않을 수 있다. 

alter session set nls_date_format = 'YYYY/MM/DD';
select sysdate,
       substr(sysdate,9,2) as 일,
       substr(sysdate,6,2) as 월,
       substr(sysdate,1,4) as 년
  from dual;

 

 

 -> 날짜 포맷을 사용자 정의에 맞게 설정 후 substr으로 출력 시 정상 출력 가능!!

 

[ 날짜 비교 ]
** 모든 비교는 비교대상과 상수가 서로 데이터타입이 일치해야 함

ex) 문자와 숫자 비교시 -> 문자를 숫자로 변환 후 비교
ex) 날짜와 문자 비교시 -> 문자를 날짜로 변환 후 비교

예제) 1981년 4월 10일 이전에 입사한 직원의 이름, 입사일 출력
select * from emp;
  

select ename, hiredate
  from EMP
 where hiredate<to_date('1981/04/10') ;
 

 

 

 정상(현재 DBMS의 날짜 포맷이 년/월/일(RR/MM/DD) 순서이므로 가능)