Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TEST (id VARCHAR(10), name VARCHAR(10), effdt DATE, node VARCHAR(20), parent VARCHAR(10));
INSERT ALL 
    INTO TEST (id,name,effdt,node,parent) VALUES ('SHA', 'DEPT', DATE '2001-01-01', 'A10200', 'TOP')    
    INTO TEST (id,name,effdt,node,parent) VALUES ('SHA', 'DEPT', DATE '2011-01-04', 'A10200', 'X10200') 
    INTO TEST (id,name,effdt,node,parent) VALUES ('SHA', 'DEPT', DATE '2014-01-01', 'A10200', 'TOP')    
    INTO TEST (id,name,effdt,node,parent) VALUES ('SHA', 'DEPT', DATE '2014-01-04', 'A10200', 'TOP')
SELECT 1 FROM DUAL;
4 rows affected

SELECT id, name, effdt,
    lag(effdt - 1, 1, null) over (partition by node order by effdt desc) effdtend,
    node, parent
FROM test
ORDER BY node, effdt;


ID
NAME
EFFDT
EFFDTEND
NODE
PARENT


SHA
DEPT
01-JAN-01
03-JAN-11
A10200
TOP

SHA
DEPT
04-JAN-11
31-DEC-13
A10200
X10200

SHA
DEPT
01-JAN-14
03-JAN-14
A10200
TOP

SHA
DEPT
04-JAN-14
null
A10200
TOP


select 
  to_char( to_date( '27-oct-17', 'dd-mon-yy' ),  'YYYY') AS d1,
  to_char( to_date( '27-oct-17', 'dd-mon-rr' ),  'YYYY') AS d2,
  to_char( to_date( '27-oct-17', 'dd-mon-yyyy'), 'YYYY') AS d3
from dual;


D1
D2
D3


2017
2017
0017

ID	NAME	EFFDT	EFFDTEND	NODE	PARENT
SHA	DEPT	01-JAN-01	03-JAN-11	A10200	TOP
SHA	DEPT	04-JAN-11	31-DEC-13	A10200	X10200
SHA	DEPT	01-JAN-14	03-JAN-14	A10200	TOP
SHA	DEPT	04-JAN-14	null	A10200	TOP