Oracle SQL - Trying to Add One Second to a Date I'm pulling from a text field

Question

SQL newbie here.

In Oracle BI Publisher, I'm using Oracle SQL to do the following.

I need to pull only the time from a string and add one second to the time. The data in the 'Flight' column looks like this:

Dayton 01:23:59

I pull only the time using the following syntax:

substr(Flight,length(Flight)-8,8)

Which gives me this:

01:23:59

In the messy code below, I'm trying to add one second to the time which works but the second is '60' which is obviously is an invalid time.

 substr(lpad(to_number(replace(substr(Flight,length(Flight)-15,9),':')+2,'999999'),6,'0'),1,2)||':'||substr(lpad(to_number(replace(substr(Flight,length(Flight)-15,9),':')+2,'999999'),6,'0'),3,2)||':'||substr(lpad(to_number(replace(substr(Flight,length(Flight)-15,9),':')+2,'999999'),6,'0'),5,2)

Any ideas for a better way to do this?

Dan

Answer 1

Can you do something like:

 to_date(substr(Flight,length(Flight)-8,8), HH:MI:SS') + interval '1' second