How to add hours to date in 24 hours format

Question

I would like to add, for example, 8 hours to the enddate in a 24 hour format. I tried adding + 8/24, 'DD-MM-YYYY HH24:MI:SS' on the first line but this gives an error.

This is my query thus far.

SELECT to_char(IN_ENDDATE, 'DD-MM-YYYY HH24:MI:SS')
  INTO IN_END_DATE_STRING
  FROM DUAL;

Answer 1

Your first line converts a date to a string. You cannot then add 8/24 to it. Do the addition before the conversion:

SELECT to_char(IN_ENDDATE + 8/24.0, 'DD-MM-YYYY HH24:MI:SS')
INTO IN_END_DATE_STRING
FROM DUAL;

IN_ENDDATE really does need to be a date type to allow +8/24 to work. If it's a timestamp, add it as an interval:

IN_ENDDATE + INTERVAL '8' HOUR

This form might be safer to use for a couple of reasons:

• it works on both date and timestamps
• it's more readable

If IN_ENDDATE is a non-date type (eg varchar) then your query works without the +8/24 because it is being successfully implicitly converted from varchar to date, before being passed to to_char. In this case either be explicit about your conversion:

SELECT to_char(to_date(IN_ENDDATE, 'YYMMDD WHATEVER') + 8/24.0, 'DD-MM-YYYY HH24:MI:SS')
INTO IN_END_DATE_STRING
FROM DUAL

SELECT to_char(to_date(IN_ENDDATE, 'YYMMDD WHATEVER') + INTERVAL '8' HOUR, 'DD-MM-YYYY HH24:MI:SS')
INTO IN_END_DATE_STRING
FROM DUAL

Or set your IN_ENDDATE parameter to really be a date type