Oracle 19c zero hour default date format with time without using any formating function

Question

I am trying to insert zero hour 20 mins date with time without using any formatting function, but I am getting some errors as follows

insert into employee(created_date) values('4/Jan/21 00:20:00 AM');

ORA-01849: hour must be between 1 to 12

how to insert zero hour in oracle 19c without using any formatting function

Answer 1

without using any formatting function

That would be a really bad idea. It means that you're about to insert a string into a DATE datatype column, hoping that Oracle will "recognize" that string, implicitly convert it to a valid DATE datatype value and insert it into a column. There are just too many "what if"s that might go wrong and - sooner or later - some of them will.

Sample table:

SQL> create table test (id number, datum date);

Table created.

This is your query:

SQL> insert into test (id, datum) values (1, '4/Jan/21 00:20:00 AM');
insert into test (id, datum) values (1, '4/Jan/21 00:20:00 AM')
                                        *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

I'll apply to_date to it, using appropriate format model (yes, I know, you don't want that - just see what happens):

SQL> insert into test (id, datum) values (1, to_date('4/Jan/21 00:20:00 AM', 'dd/Mon/yy hh:mi:ss am'));
insert into test (id, datum) values (1, to_date('4/Jan/21 00:20:00 AM', 'dd/Mon/yy hh:mi:ss am'))
                                                *
ERROR at line 1:
ORA-01843: not a valid month

How come "Jan" is not a valid month? Well, it is not. In Croatia (and that's the language my database speaks), it is "Sij". But OK, I can add additional parameter to TO_DATE:

SQL> insert into test (id, datum) values (1, to_date('4/Jan/21 00:20:00 AM', 'dd/Mon/yy hh:mi:ss am', 'nls_date_language = english'));
insert into test (id, datum) values (1, to_date('4/Jan/21 00:20:00 AM', 'dd/Mon/yy hh:mi:ss am', 'nls_date_language = english'))
                                                *
ERROR at line 1:
ORA-01849: hour must be between 1 and 12

As Zakaria commented, 00 is an invalid value; change it to 12:

SQL> insert into test (id, datum) values (1, to_date('4/Jan/21 12:20:00 AM', 'dd/Mon/yy hh:mi:ss am', 'nls_date_language = english'));

1 row created.

Not it succeeded. What did I insert? Right, that's 20 minutes past midnight.

SQL> select id, to_date(datum, 'dd.mm.yyyy hh24:mi:ss') datum from test;

        ID DATUM
---------- -------------------
         1 04.01.2021 00:20:00

SQL>

---

If you insist on what you asked, then make sure Oracle understands it.

SQL> rollback;

Rollback complete.
SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> alter session set nls_date_format = 'dd/mon/yy hh:mi:ss am';

Session altered.

SQL> insert into test (id, datum) values (1, '4/Jan/21 12:20:00 AM');

1 row created.

SQL> select * From test;

        ID DATUM
---------- ---------------------
         1 04/jan/21 12:20:00 AM

SQL>

If I were you, I wouldn't do it.

Answer 2

A DATE is a binary data type that consists of 7 bytes (century, year-of-century, month, day, hour, minute and second); it ALWAYS contains those components and is NEVER stored with any particular format.

If you want to store a data and a time then you can either use:

1. A DATE literal and an INTERVAL DAY TO SECOND literal:

   INSERT INTO employee(created_date)
   VALUES ( DATE '2021-01-04' + INTERVAL '00:20:00' HOUR TO SECOND);
   

   This requires the date in ISO 8601 format and the time in the 24-hour clock.

2. A TIMESTAMP literal (which will be implicitly cast to a DATE):

   INSERT INTO employee(created_date) VALUES ( TIMESTAMP '2021-01-04 00:20:00');
   

   This requires the date and time in ISO 8601 format (with a space instead of T between the date and time components) and the time in the 24-hour clock.

3. Explicit conversion from string-to-date:

   INSERT INTO employee(created_date) VALUES (
     TO_DATE(
       '4/Jan/21 12:20:00 AM',
       'DD/Mon/RR HH12:MI:SS AM',
       'NLS_DATE_LANGUAGE=English'
     )
   );
   

   You can explicitly set the format and language you wish to be used. (Note: in a 12-hour clock you want 12 instead of 00 for the hours.)

4. Implicit conversion from string-to-date:

   First, set the format model to be used with implicit string-to-date (or date-to-string) conversions:

   ALTER SESSION SET NLS_DATE_FORMAT = 'DD/Mon/RR HH12:MI:SS AM';
   

   Then:

   INSERT INTO employee(created_date) VALUES ('4/Jan/21 12:20:00 AM');
   

5. Something non-standard, like directly creating the binary DATE value:

   CREATE FUNCTION createDate(
     year   int,
     month  int,
     day    int,
     hour   int,
     minute int,
     second int
   ) RETURN DATE DETERMINISTIC
   IS
     hex CHAR(14);
     d DATE;
   BEGIN
    hex := TO_CHAR( FLOOR( year / 100 ) + 100, 'fm0X' )
         || TO_CHAR( MOD( year, 100 ) + 100, 'fm0X' )
         || TO_CHAR( month, 'fm0X' )
         || TO_CHAR( day, 'fm0X' )
         || TO_CHAR( hour + 1, 'fm0X' )
         || TO_CHAR( minute + 1, 'fm0X' )
         || TO_CHAR( second + 1, 'fm0X' );
     DBMS_OUTPUT.PUT_LINE( hex );
     DBMS_STATS.CONVERT_RAW_VALUE( HEXTORAW( hex ), d );
     RETURN d;
   END;
   /
   

   Then:

   INSERT INTO employee(created_date)
   VALUES (createDate(2021,1,4,0,20,0));
   

---

Options 1, 2 & 3 are standard ways of creating dates.

Option 4 is bad practice (since any user can change their own session parameters at any time so the query will fail if they NLS_DATE_FORMAT is changed from your expected format) but will work if you can guarantee the NLS_DATE_FORMAT will not be changed.

Option 5 is only really useful for creating malformed or erroneous dates for testing purposes since it bypasses the normal error checking process.

how to insert zero hour in oracle 19c without using any formatting function

The only one of those options that uses a formatting function is Option 3. However, options 1, 2 and 5 involve formatting the date differently to your format and option 4 requires you to set the implicit format model in a separate statement.