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') );
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;