7일차 # 3-43: RR Date Format

  - YY Date Format Element

  - RR Date Format Element

alter session set nls_language = american;

drop table df_test1 purge;

create table df_test1 (

 cen varchar2(5),

 naljja date);

alter session set nls_date_format = 'YYYY-MM-DD';

insert into df_test1 values ('20xx', '2012-01-09');

insert into df_test1 values ('00xx', '12-01-09');

insert into df_test1 values ('000x', '2-01-09');

insert into df_test1 values ('20xx', '01-09'); --> ORA-01840: 입력된 값의 길이가 날짜 형식에 비해 부족합니다

commit;

select * from df_test1;

insert into df_test1 values ('19xx', '1912-01-09');

insert into df_test1 values ('20xx', '2052-01-09');

insert into df_test1 values ('19xx', '1952-01-09');

commit;

select * from df_test1;

alter session set nls_date_format = 'YY-MM-DD';

select * from df_test1;

select * from df_test1

where naljja = '12-01-09';

select * from df_test1

where naljja = '52-01-09';

  - 1912년을 찾고 싶다.

select * from df_test1

where naljja = to_date('1912-01-09', 'YYYY-MM-DD');

    또는

select * from df_test1

where naljja = '1912-01-09';

    만약 애플리케이션에서 넘어온 날짜 값이 다음과 같다면: '09/01/1912'

select * from df_test1

where naljja = '09/01/1912';

↓ ↓ ↓ 

select * from df_test1

where naljja = to_date('09/01/1912', 'DD/MM/YYYY');

  - RR Format으로 변환

alter session set nls_date_format = 'RR-MM-DD';

select * from df_test1;

select * from df_test1

where naljja = '12-01-09';

select * from df_test1

where naljja = '52-01-09';

SQL> select * from df_test1

    where naljja = '2052-01-09';

==> 세기 정보를 함께 주면 RR date format이더라도 2052년을 찾을 수 있습니다.

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

drop table df_test2 purge;

create table df_test2 (

 d_format varchar2(5),

 naljja date);

alter session set nls_date_format = 'YY-MON-DD';

insert into df_test2 values ('YY', '12-JAN-09');

insert into df_test2 values ('YY', '77-SEP-09');

alter session set nls_date_format = 'RR-MON-DD';

insert into df_test2 values ('RR', '12-JAN-09');

insert into df_test2 values ('RR', '77-SEP-09');

commit;

select naljja from df_test2;

alter session set nls_date_format = 'RRRR-MM-DD'; --> RRRR은 YYYY와 똑같습니다.

select * from df_test2;

※강사님이 칠판에 몇번이나 판서를 하면서 강의를 한 부분....결국에는 모르면 YYYY를 써라 이거였음....