In Oracle 10g how can i store this format 1/4/2022 12:00:00 AM in Column of type Date-CodePudding

I want to store this 1/4/2022 12:00:00 AM in column of type Date but i get this error ORA-01843: not a valid month can i get any help if it is possible to do this in oracle

CodePudding user response：

Use to_date function with appropriate format model.

In your example, it is unclear what 1/4/2022 represents (is it 1st of April or 4th of January) so I tried to guess it.

SQL> create table test (col date);

Table created.

SQL> insert into test (col) values (to_date('1/4/2022 12:00:00 AM', 'dd/mm/yyyy hh:mi:ss am'));

1 row created.

SQL>

How to fetch that value?

One option is to do similarly as while inserting data, but this time with the to_char function:

SQL> select to_char(col, 'dd/mm/yyyy hh:mi:ss am') result from test;

RESULT
----------------------
01/04/2022 12:00:00 AM

Another option is to alter session; then select * from test returns desired result:

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

Session altered.

SQL> select * from test;

COL
----------------------
01/04/2022 12:00:00 AM

SQL>

AM/PM differentiation: Oracle knows what you entered:

SQL> insert into test (col) values (to_date('1/4/2022 2:30:00 PM', 'dd/mm/yyyy hh:mi:ss am'));

1 row created.

SQL> select * from test;

COL
----------------------
01/04/2022 12:00:00 AM
01/04/2022 02:30:00 PM

SQL>