Convert 24-hour time to 12 hours plus Oracle SQL AM / PM indication

Question

I need to do the following as an exercise, and I try my best to find a solution:

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

Invoice_date column

Use the TO_CHAR function to return the invoice_date column with its full date and time, including the four-digit year at the 24-hour hour.

Use the TO_CHAR function to return the invoice_date column with the full date and time, including the four-digit year at the 12-hour hour with the 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 cannot find a way to select the third column. Thanks. (And yes, this is from my school textbook)

Answer 1

Within 24 hours, you need to use HH24 instead of HH .

For 12 hours, the AM / PM indicator is recorded as AM (if you need periods as a result) or AM (if you do not). 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 format models that you can use with TO_CHAR per day, see http://docs.oracle.com/cd/E16655_01/server.121/e17750/ch4datetime.htm#NLSPG004 .