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)
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.
Source: Stackoverflow.com