asked on

INSERT with to_date in values clause

I'm tyring to insert a date value into a date field using to_date in the values clause, but continually get the following error. I've tried a large variety of date formats and masks, but my preferred is MM/DD/YYYY hh:mm PM TZR.

Error starting at line 1 in command:
INSERT INTO pa_cpnt_user (col_num, cpnt_typ_id, cpnt_id, rev_dte, user_value, lst_upd_usr, lst_upd_tstmp) VALUES (1124,TRNG,test-item, to_date('20070901 02:00','yyyymmdd hh:mm'),08,SS_UPDATE,SYSDATE)
Error at Command Line:1 Column:183
Error report:
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"
*Cause:
*Action:

Here is the sql:

INSERT INTO pa_cpnt_user (col_num, cpnt_typ_id, cpnt_id, rev_dte, user_value, lst_upd_usr, lst_upd_tstmp) VALUES (1124,TRNG,test-item, to_date('20070901 02:00','yyyymmdd hh:mm'),08,SS_UPDATE,SYSDATE);

My table is as follows:

desc pa_cpnt_user
Name Null Type
------------------------------ -------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COL_NUM NOT NULL NUMBER(38)
CPNT_TYP_ID NOT NULL VARCHAR2(90)
CPNT_ID NOT NULL VARCHAR2(90)
REV_DTE NOT NULL DATE
USER_VALUE VARCHAR2(120)
LST_UPD_USR VARCHAR2(90)
LST_UPD_TSTMP DATE

ASKER CERTIFIED SOLUTION

mpaladugu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mpaladugu

In the above comment, use MI instead of MM in place for minutes.

INSERT INTO pa_cpnt_user (col_num,
cpnt_typ_id,
cpnt_id,
rev_dte,
user_value,
lst_upd_usr,
lst_upd_tstmp)
VALUES (1124,
'TRNG',
'test-item',
TO_DATE ('20070901 02:00', 'yyyymmdd hh:mm'),
08,
'SS_UPDATE',
sysdate)

and to try various date formats, just to test which format returns what. and if particular format is correct use for example.
Select TO_DATE ('20070901 02:00', 'yyyymmdd hh:mi PM') from dual