Oracle date format while using TO_CHAR()

Question

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

Dates are stored in an *internal* format. Other setting define how they are seen -- and it has nothing to do with the values that are stored. — Gordon Linoff, Feb 18 '21 at 16:14
See [How are dates stored in Oracle?](https://stackoverflow.com/q/13568193/266304) and [Oracle's default data format](https://stackoverflow.com/q/50163432/266304). — Alex Poole, Feb 18 '21 at 16:26
"The standard date format in any oracle table is DD-MON-YY" NOT TRUE. An oracle DATE column is an internal binary format. (and, btw, that format includes time, down to the second). The DD-MON-YY format you mention is a result of an explicit or implicit TO_CHAR function converting that internal format to a _character string repesentation_ of the date/time, for display purposes. In addition to the previous references, see https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/ — EdStevens, Feb 18 '21 at 16:58
**Never** store date/time values as string, it's a design flaw. Use always proper `DATE` or `TIMESTAMP` data type, i.e. insert simple `SYSDATE` or `TRUNC(SYSDATE)` — Wernfried Domscheit, Feb 18 '21 at 19:29

score 1 · Answer 1 · answered Feb 18 '21 at 18:10

To directly answer your question, if the column REG_DATE is (as it should be) defined as a DATE, then it will, as all of the comments have said, be stored in oracle's internal binary format for DATEs.

And you supplied vaue of

TO_CHAR(SYSDATE,'FMmonth DD, YYYY')

will simply force in implied TO_DATE() function on the string that results from your use of TO_CHAR. That implied TO_DATE will use the format mask defined by the controlling setting of NLS_DATE_FORMAT. And if that mask does not match what you used in your TO_CHAR (and that is very unlikely) you will get an error.

SQL> create table my_table (id number,
  2                         fname varchar2(10),
  3                         reg_date date
  4                        );

Table created.

SQL>
SQL> insert into my_table (id,fname,reg_date)
  2  values (1, 'abc', TO_CHAR(SYSDATE,'FMmonth DD, YYYY') );
values (1, 'abc', TO_CHAR(SYSDATE,'FMmonth DD, YYYY') )
                  *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> --
SQL> insert into my_table (id,fname,reg_date)
  2  values (1, 'abc', SYSDATE);

1 row created.

SQL> --
SQL> select id,
  2         fname,
  3         reg_date,
  4         to_char(reg_date,'FMmonth DD, YYYY') date1,
  5         to_char(reg_date,'dd-MON-yyyy') date2,
  6         to_char(reg_date,'yyyy-mm-dd hh24:mi:ss') date3
  7  from my_table
  8  ;

 ID FNAME REG_DATE  DATE1             DATE2       DATE3
--- ----- --------- ----------------- ----------- -------------------
  1 abc   18-FEB-21 february 18, 2021 18-FEB-2021 2021-02-18 12:08:45

1 row selected.

score 0 · Accepted Answer · answered Feb 19 '21 at 05:10

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;

Oracle date format while using TO_CHAR()

2 Answers2

Linked