CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqldate-arithmetic
bhr
bhr

Reputation: 555

How to add hours to date in 24 hours format

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;

Upvotes: 1

Views: 1562

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

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

Upvotes: 8

Related Questions