Hi,
I have a Problem with formatting a date Format … i would like to Display the date like that 'DD.MM.YYYY HH24:MI:SS'.
i use an update Statement to update a view, an instead of trigger is created over the view, the trigger updates the master table.
i altered the session NLS_DATE_FORMAT in the trigger as displayed below, unfortunately the DATE is still not displayed as i wish…
Build scenario:
CREATE TABLE MYTAB (ID NUMBER(12), START_SEND DATE, ENDE_SEND DATE)
CREATE VIEW MYVIEW AS SELECT * FROM MYTAB;
create or replace TRIGGER MYTRIGGER
INSTEAD OF UPDATE ON MYVIEW
REFERENCING OLD AS OLD NEW AS NEW
DECLARE
v_nls_old VARCHAR2(40);
sql_stmt VARCHAR2(80);
BEGIN
SELECT
value
INTO
v_nls_old
FROM
nls_session_parameters
WHERE
parameter = 'NLS_DATE_FORMAT';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''DD.MM.YYYY HH24:MI:SS''';
UPDATE MYTAB
SET ID = :NEW.ID
,START_SEND = TO_DATE(:NEW.START_SEND,'DD.MM.YYYY HH24:MI:SS')
,ENDE_SEND = TO_DATE(:NEW.ENDE_SEND,'DD.MM.YYYY HH24:MI:SS')
WHERE id = :OLD.id;
sql_stmt := 'ALTER SESSION SET NLS_DATE_FORMAT = ''' || v_nls_old || '''';
EXECUTE IMMEDIATE sql_stmt;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001
,'TRIGGER - ' ||
SQLERRM);
END MYTRIGGER;
/
insert into mytab(id) values (1);
commit;
update myview
set id=2, START_SEND=to_date('06.06.2019 10:00:00','DD.MM.YYYY HH24:MI:SS'),ENDE_SEND=to_date('06.06.2019 12:00:00','DD.MM.YYYY HH24:MI:SS') where id=1;
commit;
select * from myview;
ID START_SEND ENDE_SEND
---------- -------- --------
2 06.06.19 06.06.19Could you please tell me where is the Problem?
Thank you
The problem is using a trigger ;)
Seriously, there's no need. You can update the view and the database will resolve this to the base table:
CREATE TABLE MYTAB (ID NUMBER(12), START_SEND DATE, ENDE_SEND DATE);
CREATE VIEW MYVIEW AS SELECT * FROM MYTAB;
insert into mytab(id) values (1);
commit;
update myview
set id = 2,
START_SEND=to_date('06.06.2019 10:00:00','DD.MM.YYYY HH24:MI:SS'),
ENDE_SEND=to_date('06.06.2019 12:00:00','DD.MM.YYYY HH24:MI:SS')
where id = 1;
commit;
select * from myview;
ID START_SEND ENDE_SEND
2 06-JUN-2019 10:00:00 06-JUN-2019 12:00:00 Unless your real view is way more complex, just update the view.
But even if you need the trigger, setting the NLS format is a waste. The columns are already dates, so there's no need to to_date them again in the trigger.
Secondly, after the update the trigger reverts the format back to whatever it was before it fired. Which is why you don't have the format used inside the trigger.
Remember: clients can (and do!) set the NLS_DATE_FORMAT to whatever they want.
If you want to be certain of displaying a date in a given format, you should to_char it:
alter session set nls_date_format = 'YYYY-MM-DD';
select * from myview;
ID START_SEND ENDE_SEND
2 2019-06-06 2019-06-06
select id, to_char ( start_send, 'DD.MM.YYYY' ),
to_char ( ende_send, 'DD.MM.YYYY' )
from myview;
ID TO_CHAR(START_SEND,'DD.MM.YYYY') TO_CHAR(ENDE_SEND,'DD.MM.YYYY')
2 06.06.2019 06.06.2019