19

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)

Galen_GG
  • 310
  • 1
  • 2
  • 6
  • Possible duplicate of [Oracle to\_date with p.m./a.m](http://stackoverflow.com/questions/27269903/oracle-to-date-with-p-m-a-m) – Tony L. Oct 02 '16 at 01:30

2 Answers2

44

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.

Community
  • 1
  • 1
ruakh
  • 175,680
  • 26
  • 273
  • 307
  • 2
    @Galen_GG Actually, for 12-hour time-format, it's usually better *not* to include a leading zero in the output hour format (`03:50 PM` just looks wrong, since the leading zero implies a 24h clock in most cases!!). Better therefore to use: `TO_CHAR(invoice_date, 'DD-MM-YYYY FMHH:MI:SS AM') "Date 12Hr"` – cartbeforehorse Mar 22 '16 at 15:11
  • @cartbeforehorse Thank you for how to remove the leading 0 in the hours. – Fering Jan 28 '19 at 19:46
1

'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')
Puneet Poojary
  • 19
  • 1