I have 2 columns in my table both of varchar2. I have a query like
SELECT MYCOLUMN_TIME||' '||MYCOLUMN_TIME_AMPM
FROM MYTABLE
I am getting output 0910 am. I have tried
SELECT TO_CHAR(TO_DATE(MYCOLUMN_TIME,'hh24miss'), 'hh24:mi:ss')||' '||MYCOLUMN_TIME_AMPM
FROM MYTABLE
With this query I am getting Output 09:10:00 pm.
I want Output like 21:10:00 pm. How can we achieve this? Please help.
When using HH24 format mask, 21 hours equals 9 PM. There's no point in having PM along with 21, is there?
When you convert a string (0910) concatenated with am/pm to a date, you use TO_DATE function with appropriate format mask. Date values - in Oracle - contain both date and time component (see datum_1 in the following example). Then apply TO_CHAR to such a result in order to display it as you want (again, by applying desired format mask) - that's datum_2.
For example:
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh:mi pm';
Session altered.
SQL> with mytable (mycolumn_time, mycolumn_time_ampm) as
2 (select '0910', 'am' from dual union all
3 select '1150', 'pm' from dual
4 )
5 select mycolumn_time,
6 mycolumn_time_ampm,
7 to_date(mycolumn_time ||' '||mycolumn_time_ampm, 'hhmi pm') datum_1,
8 --
9 to_char(to_date(mycolumn_time ||' '||mycolumn_time_ampm, 'hhmi pm'), 'hh24:mi') datum_2
10 from mytable;
MYCO MY DATUM_1 DATUM_2
---- -- ------------------- -------
0910 am 01.09.2020 09:10 AM 09:10
1150 pm 01.09.2020 11:50 PM 23:50
SQL>
If you add PM format mask, you'd get
<snip>
9 to_char(to_date(mycolumn_time ||' '||mycolumn_time_ampm, 'hhmi pm'), 'hh24:mi pm') datum_2
10 from mytable; ^^
here
MYCO MY DATUM_1 DATUM_2
---- -- ------------------- --------
0910 am 01.09.2020 09:10 AM 09:10 AM
1150 pm 01.09.2020 11:50 PM 23:50 PM
SQL>
but - as I previously said - it doesn't make sense. There's no e.g. 23:50 AM, but it makes perfect sense in 11:50 AM or 11:50 PM.