CODEWITHSUNDEEP
oracle-database
user2160918
user2160918

Reputation: 11

Convert time stored as hh24miss to hh24:mi:ss

I have a column in my table which will store the time as hh24miss format, i.e it stores as 091315 which is 09 hrs 13 min 15 sec. I need to convert it into HH24:MI:SS AND concatenate it with the date column which is in YYYYMMDD format.

Simply, the following columns Date: 19940601 and Time: 091315 need to be converted to 01-Jan-94 09:13:15.

Upvotes: 0

Views: 853

Answers (1)

Alex Poole
Alex Poole

Reputation: 191305

You should not store dates as strings, and there is no need to store the time in a separate field. Oracle's DATE data type includes times down the to the second. (You'd need TIMESTAMP for fractions of a second).

If you are really stuck with this schema then you need to convert the two strings into a DATE:

to_date(date_column || time_column, 'YYYYMMDDHH24MISS')

You can then display that in whatever format you want; what you showed would be:

to_char(to_date(date_column || time_column, 'YYYYMMDDHH24MISS'),
  'DD-Mon-RR HH24:MI:SS')

Although the data you have is June, not January.

SQL Fiddle demo.

But really, please revisit your schema and use the appropriate data type for this, don't store the values as strings. You have no validation and no easy way to check that the values you have stored actually represent valid dates and times.

Upvotes: 3

Related Questions