By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (id, updtd_dt) AS
SELECT '123', CAST(NULL AS DATE) FROM DUAL
1 rows affected
CREATE VIEW view1 (end_ts) AS
SELECT DATE '2022-03-25' + INTERVAL '10:14:44' HOUR TO SECOND FROM DUAL;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-- Errors if your NLS_DATE_FORMAT does not match the explicit format model.
UPDATE Table1
SET UPDTD_DT = ( SELECT TO_DATE(END_TS-1/(24*60*60),'DD/MM/YYYY HH24:MI:SS')
FROM View1 )
WHERE id = '123';
ORA-01861: literal does not match format string
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YY';
-- Truncates the time component to midnight if the implicit cast from a
-- date to a string does not include the time component.
UPDATE Table1
SET UPDTD_DT = ( SELECT TO_DATE(END_TS-1/(24*60*60),'DD/MM/YYYY HH24:MI:SS')
FROM View1 )
WHERE id = '123';
1 rows affected
SELECT id, TO_CHAR(updtd_dt, 'YYYY-MM-DD HH24:MI:SS') AS updtd_dt FROM table1;
| ID | UPDTD_DT |
|---|---|
| 123 | 0022-03-25 00:00:00 |
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
-- Works if you have a matching NLS_DATE_FORMAT
UPDATE Table1
SET UPDTD_DT = ( SELECT TO_DATE(END_TS-1/(24*60*60),'DD/MM/YYYY HH24:MI:SS')
FROM View1 )
WHERE id = '123';
1 rows affected
SELECT id, TO_CHAR(updtd_dt, 'YYYY-MM-DD HH24:MI:SS') AS updtd_dt FROM table1;
| ID | UPDTD_DT |
|---|---|
| 123 | 2022-03-25 10:14:43 |
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-- But you should NEVER use TO_DATE on a DATE then you don't need to worry
-- what the NLS_DATE_FORMAT is set to and it will work.
UPDATE Table1
SET UPDTD_DT = ( SELECT END_TS - INTERVAL '1' SECOND FROM View1 )
WHERE id = '123';
1 rows affected
SELECT id, TO_CHAR(updtd_dt, 'YYYY-MM-DD HH24:MI:SS') AS updtd_dt FROM table1;
| ID | UPDTD_DT |
|---|---|
| 123 | 2022-03-25 10:14:43 |