Clash Royale CLAN TAG

Oracle SQL - Using TO_DATE to add a second to a time pulled from a string

This is building on the question I asked yesterday Link

I'm pulling time from the 'Flight' column whose data looks like this:

Dayton 01:23:59


Which gives me this:

01:23:59


I then want to add 1 second to this. I'm using the following TO_DATE function:

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


This works but the format includes the date:

2018-07-01T01:24:00.000+00:00


I need it to look like this:

01:24:00


I've tried using the SUBSTR to extract only the time to no avail.
Any ideas on how I can add 1 second to the above and preserve the HH24:MI:SS format?

to_char()

1 Answer
1

The direct way is using to_char string conversion function with HH24:MI:SS pattern as

to_char

HH24:MI:SS

with t(myDate) as
(
select to_date(substr('Dayton 01:23:59',length('Dayton 01:23:59')-8,8), 'HH:MI:SS')
+ interval '1' second
from dual
)
select to_char(myDate, 'hh24:mi:ss') from t;

MYDATE_CHR
01:23:06


or alternatively regexp_replace function maybe used as :

regexp_replace

select regexp_replace('Dayton 01:23:59','[^0-9:]') as myDate_chr
from dual;

MYDATE_CHR
01:23:06


P.S. dual maybe replaced by your real table name.

But seperating this column into two seperate columns as city and time is better.

SQL Fiddle Demo

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.