CREATE OR REPLACE FUNCTION shift_date_by (date_in IN DATE,
days_in IN INTEGER DEFAULT 0,
hours_in IN INTEGER DEFAULT 0,
minutes_in IN INTEGER DEFAULT 0,
seconds_in IN INTEGER DEFAULT 0)
RETURN DATE
IS
BEGIN
/* Inspired by @intsiful
http://bkintsiful.blogspot.com/2012/04/how-does-one-add-dayhourminutesecond-to.html */
RETURN date_in
+ days_in
+ (hours_in / 24)
+ (minutes_in / 1440)
+ (seconds_in / 86400);
END;
Function created.
DECLARE
c_start DATE := TO_DATE ('2016-10-12 15:15:15', 'YYYY-MM-DD HH24:MI:SS');
PROCEDURE show_date (date_in IN DATE)
IS
BEGIN
DBMS_OUTPUT.put_line (TO_CHAR (date_in, 'YYYY-MM-DD HH24:MI:SS'));
END;
BEGIN
show_date (shift_date_by (c_start, 1));
show_date (shift_date_by (c_start, -1));
show_date (shift_date_by (c_start, 0, 1));
show_date (shift_date_by (c_start, 0, -1));
show_date (shift_date_by (c_start,
0,
0,
10));
show_date (shift_date_by (c_start,
0,
0,
-10));
show_date (shift_date_by (c_start,
0,
0,
0,
10));
show_date (shift_date_by (c_start,
0,
0,
0,
-10));
END;
2016-10-13 15:15:15
2016-10-11 15:15:15
2016-10-12 16:15:15
2016-10-12 14:15:15
2016-10-12 15:25:15
2016-10-12 15:05:15
2016-10-12 15:15:25
2016-10-12 15:15:05