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
>Solution :
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>