Convert 24 Hour Time to 12 Hour Plus Am/Pm Indication Oracle SQL

Convert 24 Hour time to 12 Hour plus AM/PM indication Oracle SQL

For the 24-hour time, you need to use HH24 instead of HH.

For the 12-hour time, the AM/PM indicator is written as A.M. (if you want periods in the result) or AM (if you don't). For example:

SELECT invoice_date,
       TO_CHAR(invoice_date, 'DD-MM-YYYY HH24:MI:SS') "Date 24Hr",
       TO_CHAR(invoice_date, 'DD-MM-YYYY HH:MI:SS AM') "Date 12Hr"
  FROM invoices
;

For more information on the format models you can use with TO_CHAR on a date, see http://docs.oracle.com/cd/E16655_01/server.121/e17750/ch4datetime.htm#NLSPG004.

Extract hour from timestamp in 12 hour format with AM/PM indicator oracle

Since you are talking about formats, you are not simply "extracting the hour" - you are really extracting a string.

TO_CHAR(subdate, 'hh.mi AM')

should do the trick.

ADDED: Based on comments. If you want : separator instead of . that is easy, just change it in the format mask. If you want to round down to the hour use trunc(subdate, 'hh') instead of subdate. If you need to drop the leading zero, you can wrap the entire expression in LTRIM(... , '0') or, as Alex Poole suggest, use the fm toggle like this: 'fmhh:fmmi AM'

Oracle to_date with p.m./a.m

Try this:

to_date
  ( '08/11/1999 05:45:00 p.m.'
  , 'dd/mm/yyyy hh:mi:ss a.m.'
  , 'nls_date_language=american'
  )

It seems that "a.m." and "p.m." rather than "am" and "pm" require nls_date_language to be set to "american".

SQL How do I extract time from a date to HH:MI:SS PM/AM?

You can use TO_CHAR( datetime, format ) to format a DATE column. The format options are given here.

This would give you the time part of a date value (12-hour clock plus AM/PM):

SELECT TO_CHAR( column_name,'HH:MI:SS AM') 
FROM   your_table

Edit - Addressing the update

You can do:

SELECT CASE WHEN TO_NUMBER( TO_CHAR( a.dtime_appl_creation, 'HH24' ) ) >= 12
            THEN TO_CHAR( a.dtime, 'HH:MI:SS PM' )
            END AS "date"
FROM   table_name

How to order a VARCHAR2 column representing date and time with AM and PM Oracle SQL?

Maybe I am wrong but i do not see nothing wrong here:

select to_char(to_date(Date_c,'mm/dd/yyyy hh:mi:ss AM'),'mm/dd/yyyy hh:mi:ss AM') 
from myTable
order by to_date(Date_c,'mm/dd/yyyy hh:mi:ss AM');

That is whit this:

select *
from myTable
order by to_date(Date_c,'mm/dd/yyyy hh:mi:ss AM');

Here is the DEMO

---

Related Topics