Date problem

[supercbw]

Can someone give me a better explanation as to what this error means

ORA-01820: format code cannot appear in date input format

[svk]

will be pretty simple to explain if you post the statement that is generating this error...

[supercbw]

passing the following variables to following procedure:

exec fmgdba.fmgbatch.resend_afe_upd(001111, 3, 'WDA','2001-11-01', 'christest.WD
A.001111', 'christest.WDA.001111.000001', 'E', 'christian.witucki@us.hsbc.com',
'resend', '2001-11-01', 'resent', sysdate, sysdate, 'Y', 000002, '', '', 'christ
test 1', :v_out);




PROCEDURE RESEND_AFE_UPD (
P_RID IN NUMBER,
P_SEQ_NO IN NUMBER,
P_APPL_CODE IN VARCHAR2,
P_REC_DATE IN VARCHAR2,
P_INFILE IN VARCHAR2,
P_OUTFILE IN VARCHAR2,
P_TYPE IN VARCHAR2,
P_DEST IN VARCHAR2,
P_COMMENTS IN VARCHAR2,
P_WORK_DATE IN VARCHAR2,
P_STATUS IN VARCHAR2,
P_SENT_DATE IN DATE,
P_LAST_CH_D IN DATE,
P_RESEND_F IN VARCHAR2,
P_ORIG_SEQ_NO IN NUMBER,
P_ATT_NAME IN VARCHAR2,
P_ATT_ALIAS IN VARCHAR2,
P_SUBJECT IN VARCHAR2,
P_OUT OUT NUMBER) AS


V_ERRORTEXT VARCHAR2(200);

BEGIN


insert into request
(request_id, request_seq_no, appl_code, received_date, input_file_name, output_msg_file_name,
trans_type, destination, comments, work_of_date, status,
sent_date, last_stat_chg_date, resend_flag, original_seq_no, att_name, att_alias, subject)
values
(P_RID, P_SEQ_NO, P_APPL_CODE, TO_DATE(P_REC_DATE,'CCYY-MM-DD'), P_INFILE, P_OUTFILE, P_TYPE, P_DEST, P_COMMENTS,
TO_DATE(P_WORK_DATE,'CCYY-MM-DD'), P_STATUS, P_SENT_DATE, P_LAST_CH_D, P_RESEND_F, P_ORIG_SEQ_NO, P_ATT_NAME, P_ATT_ALIAS, P_SUBJECT);

COMMIT;

P_OUT := 0;
return;

[svk]

Change that 'CCYY' to 'YYYY'

[sonofsita]

this can not be specified in to_date function

TO_DATE(P_WORK_DATE,'CCYY-MM-DD')

it can be used in to_char

there also you will get the output

century||last two digits of year-month-date

you want that..




[Edited by sonofsita on 11-19-2001 at 01:06 PM]

[zainalj]

change CCYY-MM-DD to RRRR-MM-DD where RRRR will represent the century