CODEWITHSUNDEEP
sqloracle-databaseplsql
Kyujiin
Kyujiin

Reputation: 109

Add days/hours/minutes and months to time stamp

Is there a way to add days hours months and minutes into a timestamp that i want to insert the value of into a variable

I have separate variables for minutes,hours,days and months that I want to add to the timestamp

the timestamp format goes like this '04-FEB-21 10.25.12.013000 AM'

I tried using SELECT TO_TIMESTAMP(datecreated,'dd-mon-yyyy hh.mi.ss AM') + daysvar into duedate FROM dual; but it returns the error AM/A.M. or PM/P.M. required

daysvar contains the amount of days to be added to the timestamp

Thank you!

Upvotes: 0

Views: 587

Answers (1)

MT0
MT0

Reputation: 167982

I have separate variables for minutes,hours,days and months that I want to add to the timestamp

Use NUMTODSINTERVAL:

DECLARE
  datecreated VARCHAR2(30) := '04-FEB-21 10.25.12.013000 AM';
  days    INT := 1;
  hours   INT := 2;
  minutes INT := 42;
  seconds INT := 3;
  duedate VARCHAR2(30);
BEGIN
  duedate := TO_CHAR(
               TO_TIMESTAMP(
                 datecreated,
                 'DD-MON-RR HH12.MI.SS.FF6 AM',
                 'NLS_DATE_LANGUAGE=American'
               )
               + NUMTODSINTERVAL(days,    'DAY')
               + NUMTODSINTERVAL(hours,   'HOUR')
               + NUMTODSINTERVAL(minutes, 'MINUTE')
               + NUMTODSINTERVAL(seconds, 'SECOND'),
               'DD-MON-RR HH12.MI.SS.FF6 AM',
               'NLS_DATE_LANGUAGE=American'
             );
  DBMS_OUTPUT.PUT_LINE(duedate);
END;
/

db<>fiddle here

If you want to add months then use ADD_MONTHS; however, it returns a DATE (and not a TIMESTAMP) so you would lose the fractional seconds. You can add those back though:

DECLARE
  datecreated VARCHAR2(30) := '04-FEB-21 10.25.12.013000 AM';
  months  INT := 4;
  days    INT := 1;
  hours   INT := 2;
  minutes INT := 42;
  seconds INT := 3;
  created_date TIMESTAMP;
  duedate VARCHAR2(30);
BEGIN
  created_date := TO_TIMESTAMP(
                    datecreated,
                    'DD-MON-RR HH12.MI.SS.FF6 AM',
                    'NLS_DATE_LANGUAGE=American'
                 );
  duedate := TO_CHAR(
               CAST(ADD_MONTHS(created_date, months) AS TIMESTAMP)
               + NUMTODSINTERVAL(days,    'DAY')
               + NUMTODSINTERVAL(hours,   'HOUR')
               + NUMTODSINTERVAL(minutes, 'MINUTE')
               + NUMTODSINTERVAL(seconds, 'SECOND')
               + (created_date - CAST(created_date AS DATE)), -- Fractional seconds
               'DD-MON-RR HH12.MI.SS.FF6 AM',
               'NLS_DATE_LANGUAGE=American'
             );
  DBMS_OUTPUT.PUT_LINE(duedate);
END;
/

db<>fiddle here

Upvotes: 5

Related Questions