Oracle date format while using TO_CHAR()

The standard date format in any oracle table is DD-MON-YY, but I still wonder for the below query if the date will get stored into reg_date column in ‘DD-MON-YY’ format as it is the Oracle standard or will it get stored as per the ‘FMmonth DD, YYYY’ format?

insert into table (id,name,reg_date) 
values (1, 'abc', TO_CHAR(SYSDATE,'FMmonth DD, YYYY') );

Answer

Lets see what Oracle actually does when you insert into a date column using various formats: ISO Standard, Oracle’s NLS_DATE_FORMAT specification, and a format I just made up. Then a couple queries, and finally, with the DUMP function, a peek inside. (Also see here for slightly different set.)

create table date_examples( date_1 date, date_2 date, date_3 date);

alter session set nls_date_format = 'dd-Mon-yyyy';   -- set default

-- set the same date in verious formats: ISO Standare, Declared Standard, a strange format
insert into date_examples( date_1, date_2, date_3) 
  select date '2021-02-18'                   -- standard iso format 
       , to_date('18-Feb-2021')              -- defaulr see alter session 
       , to_date('18 2021 02', 'dd yyyy mm') -- specified
  from dual;

-- what are the various dates without specifying how   
select * from date_examples;

-- now change the default
alter session set nls_date_format = 'Month dd, yyyy hh24:mi:ss';   -- set default
select * from date_examples;

-- take a peek at the inside. 
select dump(date_1), dump(date_2), dump(date_3) 
  from date_examples;