reperesent AM or PM values with time in oracle

Question

i have a table with two date columns: time_from,time_to.Both have datatype of date.Now,I want to concatenate these column values such that I can get only times like this:

9:00 PM-9:50 PM.

Now,some of my times are in AM while others are PM and I want to write a select statement to concatenate them.I wrote this but it is not returning "AM" or "PM" correct everytime.

My query is:

select 
  to_char(time_from,'HH:MI:SS PM') || ' - ' || to_char(time_to,'HH:MI:SS PM')
from
  table-name

I had initially inserted my time in 12 hr format.

Need some guidance here please? I don't want to hard code AM or PM

Check this : http://stackoverflow.com/questions/14987473/convert-24-hour-time-to-12-hour-plus-am-pm-indication-oracle-sql — Atri, Nov 12 '15 at 05:22

Lalit Kumar B · Answer 1 · 2015-11-12T06:18:36.297

Now,some of my times are in AM while others are PM

DATE is not stored in the database the way you see it. What you see is only for display purpose for us to interpret it. Oracle stores date in an internal format of 7 bytes which is it's proprietary format.

but it is not returning "AM" or "PM" correct everytime

You need to validate your data as TO_CHAR doesn't manipulate the data. It will display the datetime in your desired format.

It only depends on the DATE value in your table, and is independent of the format model AM/PM in TO_CHAR.

-- setting nls_date_format only for the DATE value display

SQL> alter session set nls_date_format='MM/DD/YYYY HH:MI:SS PM';

Session altered.

PM format:

SQL> WITH DATA AS
  2    ( SELECT SYSDATE - 4/24 from_date, SYSDATE + 4/24 to_date FROM dual
  3    )
  4  SELECT from_date,
  5    to_date,
  6    TO_CHAR(from_date, 'HH:MI PM')
  7    ||' - '
  8    ||TO_CHAR(to_date, 'HH:MI PM') my_tm_format
  9  FROM DATA;

FROM_DATE              TO_DATE                MY_TM_FORMAT
---------------------- ---------------------- -------------------
11/12/2015 07:39:05 AM 11/12/2015 03:39:05 PM 07:39 AM - 03:39 PM

AM format:

SQL> WITH DATA AS
  2    ( SELECT SYSDATE - 4/24 from_date, SYSDATE + 4/24 to_date FROM dual
  3    )
  4  SELECT from_date,
  5    to_date,
  6    TO_CHAR(from_date, 'HH:MI AM')
  7    ||' - '
  8    ||TO_CHAR(to_date, 'HH:MI AM') my_tm_format
  9  FROM DATA;

FROM_DATE              TO_DATE                MY_TM_FORMAT
---------------------- ---------------------- -------------------
11/12/2015 07:39:14 AM 11/12/2015 03:39:14 PM 07:39 AM - 03:39 PM

NOTE:

The WITH clause is only to create the sample data for demonstration. Use the following query with your table, which is the same as you are using currently. The point is that you need to validate your data as TO_CHAR doesn't manipulate the data.

SELECT from_date,
  to_date,
  TO_CHAR(from_date, 'HH:MI PM')
  ||' - '
  ||TO_CHAR(to_date, 'HH:MI PM') my_tm_format
FROM your_table;

TO_CHAR is capable to convert the time element in 12-hour format irrespective of the format model you specify for the time element.

thanks..can you modify "With Data" clause to select from my table aka (Example).I am getting confused how my table name will reside in this select statement — user3400060, Nov 12 '15 at 06:11

score 0 · Answer 2 · answered Nov 12 '15 at 15:43

According to desired output (no leading zero on hour) it should be this one:

SELECT 
  TO_CHAR(time_from, 'fmHH:')||TO_CHAR(time_from, 'MI PM') || ' - ' ||
  TO_CHAR(to_from, 'fmHH:')||TO_CHAR(to_from, 'MI PM')
FROM table_name;

or

SELECT 
  REGEXP_REPLACE(TO_CHAR(time_from, 'HH:MI PM'), '^0') || ' - ' ||
  REGEXP_REPLACE(TO_CHAR(to_from,'HH:MI PM'), '^0')
FROM table_name;

reperesent AM or PM values with time in oracle

2 Answers2