CODEWITHSUNDEEP
oracle-databaseoracle12ccognoscognos-10
Riley Mashburn
Riley Mashburn

Reputation: 5

Convert Varchar to ANY time format

I have a varchar column called begin_time that stores the 24-hour time as a varchar with no time formatting, ie 1330

I need to convert this varchar to a usable timestamp, datetime, etc. where I can easily convert the varchar to a standard time format (1:30 PM)

The end format type doesn't matter as long as I can convert the varchar into a format that I can manipulate to a standard format.

I've tried looking into Cognos-specific format tricks (These functions are for Metric Designer, and I'm using Report Studio) to no avail. The methods I found when looking for oracle-specific tricks seemed to be way too convoluted (using insanely long regex rules) for what I need.

If I need to have a date involved, I can use the column start_date and append the varchar time. Note: start_date is in the date format

Example

select 
to_date('08/27/2018','MM/DD/YYYY') as start_date
, '1300' as begin_time
from dual

What I ultimately need is just to be able to output the time as 1:00 PM

Any help would be appreciated. I'm beating my head against the wall on this... I'm used to using proprietary codes for periods of time and don't have a lot of experience with the true datetime formats.

Updates answering questions

Thank you all for the fast responses. I might have hair left when this request is over now :)

Final Thought

My lesson learned from this is simple: If you're dealing with time formats, don't throw out the idea of using a Date format function.

Upvotes: 0

Views: 1100

Answers (1)

Littlefoot
Littlefoot

Reputation: 142723

Looking for this?

SQL> with test (col) as
  2    (select '1330' from dual)
  3  select to_char(to_date(col, 'hh24mi'), 'hh:mi am') result
  4  from test;

RESULT
--------
01:30 PM

SQL>

What does it do?

  • TO_DATE converts string you have (such as 1330) into a valid DATE value. By default, it'll be a date value truncated to the first of current month:

    SQL> alter session set nls_Date_format = 'dd.mm.yyyy hh24:Mi';

Session altered.

SQL> select to_date('1330', 'hh24mi') res1 from dual;

RES1
----------------
01.04.2019 13:30

SQL>

  • applying TO_CHAR to it, again with the appropriate format mask, returns the desired result

Upvotes: 2

Related Questions