Retrieve hours and minutes with meridiem indicator (am/pm) in Oracle-CodePudding

I have hours and minutes in different columns like below

Hour of Day	Minute of Day
10	50
15	00

By using these values I need the exact day time like the result of above table should be

Expected Result
10:50 AM
03:00 PM

How can I achieve this by using Oracle or SQL? Can someone please help me onto this.

CodePudding user response：

Here's one option - first create a DATE value (concatenate hours and minutes, apply appropriate format model); for the final result, apply TO_CHAR to it, again with desired format.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> with test (hour, minute) as
  2    (select '10', '50' from dual union all
  3     select '15', '00' from dual
  4    )
  5  select hour, minute,
  6            to_date(hour||':'|| minute, 'hh24:mi') date_value,
  7    to_char(to_date(hour||':'|| minute, 'hh24:mi'), 'hh:mi am') result
  8  from test;

HO MI DATE_VALUE          RESULT
-- -- ------------------- ------------------------------
10 50 01.03.2022 10:50:00 10:50 AM
15 00 01.03.2022 15:00:00 03:00 PM

SQL>