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

Question

I am required to do the following as an exercise, and I am struggling to find a solution:

Write a SELECT statement that returns these columns from the Invoices table:

The invoice_date column

Use the TO_CHAR function to return the invoice_date column with its full date and time including a four-digit year on a 24-hour clock

Use the TO_CHAR function to return the invoice_date column with its full date and time including a four-digit year on a 12-hour clock with an am/pm indicator.

Use the CAST function to return the invoice_date column as VARCHAR2(10)

All I can get is:

    select invoice_date, to_char(invoice_date, 'DD-MM-YYYY HH:MM:SS') "Date 24Hr"
    from invoices

Which gets my first two columns, however I can't figure out any way to select the third column. Any help would be great, thanks. (And yes, this is from my school textbook)

Answer 1

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.

Answer 2

'hh' is for 12 hour format , 'HH' is for 24 format and 'tt' is for AM/PM

SELECT FORMAT(invoice_date, N'dd-MMM-yyyy hh:mm:ss tt')