Insert sysdate using french format(dd/mm/yyyy) to date field in plsql

Question

I have table that contain field 'EVT_START_DATE' with datatype is DATE on Oracle. I want to insert the current date into this field, with french format dd/mm/yyyy.

I did this command line:

ALTER SESSION SET NLS_LANGUAGE = 'FRENCH';

After that, i tried this block of PL/SQL code:

DECLARE
v_evt_num VARCHAR(200); 
BEGIN
v_evt_num := 'PC_' || evt_seq.NEXTVAL; 

INSERT INTO event
(EVT_NUM,EVT_CODE_PAY,EVT_CODE_USER,EVT_START_DATE,EVT_MT) 
VALUES 
(v_evt_num,
'129',
'247',
TO_DATE(SYSDATE, 'DD/MM/YYYY'),
:i_mt);

COMMIT;

dbms_output.put_line('Success.');

EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line('Fail.');

END;

But I get fail message.

It would help if you share the error message with us. Also, what is i_mt I don't see it declared anywhere. When you use a variable just use the variable name without the : in front of it. Display the error message instead of just "Fail". — Cyrille MODIANO
Remove the exception handling so you can see the real error. — Cyrille MODIANO
After add sqlerrm i get : ORA-01843: not a valid month ORA-02063: preceding line from event — dpccrt87
use this: INSERT INTO event (EVT_NUM,EVT_CODE_PAY,EVT_CODE_USER,EVT_START_DATE,EVT_MT) VALUES (v_evt_num, '129', '247', SYSDATE, i_mt); — Cyrille MODIANO

krokodilko krokodilko · Accepted Answer · 2017-09-13T15:26:08

If EVT_START_DATE is of type DATE, then use just:

INSERT INTO event
(EVT_NUM,EVT_CODE_PAY,EVT_CODE_USER,EVT_START_DATE,EVT_MT) 
VALUES 
(v_evt_num,
'129',
'247',
SYSDATE,
:i_mt);

whithout usingto_date function.

Oracle's SYSDATE function returns value of type DATE,
so there is no need to use to_date in order to convert it to DATE, bacause it is DATE yet.

SYSDATE returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE

Insert sysdate using french format(dd/mm/yyyy) to date field in plsql

1 Answers