Convert 24 Hour Time To 12 Hour Plus Am/pm Indication Oracle Sql

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_d

Solution 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.

Solution 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')