SQL*Plus doesn’t really format dates at all. If you are selecting a date column from the database, you must use Oracle’s built-in TO_CHAR function to convert the date to a character string, formatting it the way you want it. As far as SQL*Plus is concerned, that makes it just another character column. Table 2.3 shows the date format elements that can be used with the TO_CHAR function.
The one SQL*Plus command that does recognize these date format elements is the ACCEPT command. When you ask the user to enter a date, you can also provide a date format specification. SQL*Plus will reject any date the user enters that does not match that format.
Table B-3. Date Format Elements
|
Format Element |
Function |
|---|---|
|
-/,.;: |
Punctuation may be included anywhere in the date format string, and will be included in the output. |
|
`text’ |
Quoted text may also be included in the date format string, and will be reproduced in the output. |
|
AD or A.D. BC or B.C. |
Includes an AD or BC indicator with the date. |
|
AM or A.M. PM or P.M. |
Prints AM or PM, whichever applies, given the time in question. |
|
CC |
The century number. This will be 20 for years 1900 through 1999. |
|
SCC |
Same as CC, but BC dates will be negative. |
|
D |
The number of the day of the week. This will be 1 through 7. |
|
DAY |
The name of the day. This will be Saturday, Sunday, Monday, and so forth. |
|
DD |
The day of the month. |
|
DDD |
The day of the year. |
|
DY |
The abbreviated name of the day. This will be Sat, Sun, Mon, and so forth. |
|
HH |
The hour of the day. This will be 1 through 12. |
|
HH12 |
The hour of the day. This will be 1 through 12, the same as HH. |
|
HH24 |
The hour of the day on a 24-hour clock. This will be 0-23. |
|
IW |
The week of the year. This will be 1-53. |
|
IYYY |
The four-digit year. |
|
IYY |
The last three digits of the year number. |
|
IY |
The last two digits of the year number. |
|
I |
The last digit of the year number. |
|
J |
The Julian day. Day 1 is equivalent to Jan 1, 4712 BC. |
|
MI |
The minute. |
|
MM |
The month number. |
|
MON |
The three-letter month abbreviation. |
|
MONTH |
The month name, fully spelled out. |
|
Q |
The quarter of the year. Quarter 1 is Jan-Mar, quarter 2 is Apr-Jun, and so forth. |
|
RM |
Is the month number in Roman numerals. |
|
RR |
When used with TO_CHAR, returns the last two digits of the year. |
|
RRRR |
When used with TO_CHAR, returns the four-digit year. |
|
SS |
The second. |
|
SSSSS |
The number of seconds since midnight. |
|
WW |
The week of the year. |
|
W |
The week of the month. Week one starts on the first of the month. Week two starts on the 8th of the month, and so forth. |
|
Y,YYY |
The four-digit year with a comma after the first digit. |
|
YEAR |
The year spelled out in words. |
|
SYEAR |
The year spelled out in words, with a leading negative sign when the year is BC. |
|
YYYY |
The four-digit year. |
|
SYYYY |
The four-digit year, with a leading negative sign when the year is BC. |
|
YYY |
The last three digits of the year number. |
|
YY |
The last two digits of the year number. |
|
Y |
The last digit of the year number. |
When displaying a date, you must use the TO_CHAR function to specify the format. The following example displays the current value of SYSDATE, including the time:
SQL>SELECT TO_CHAR(SYSDATE,'dd-Mon-yyyy hh:mi:ss PM')2FROM dual;TO_CHAR(SYSDATE,'DD-MON ----------------------- 13-Dec-1998 09:13:59 PM
When you use a date format element that displays a text value, such as the name of a month, you need to pay attention to the case. The case of the element displayed will follow the case used when you specified the element. Suppose you want to display the three-letter abbreviation for a month. You could place either “Mon”, “mon”, or “MON” in your format string, and you would get back “Dec”, “dec”, or “DEC” respectively. You will see examples of this in Table 2.4, which shows the results of several sample date format specifications.
To find out how to use a date format with the ACCEPT command, consult Chapter 7. ACCEPT uses the date format to validate what the user enters, and there are some limits on how closely the user is forced to follow that format.
Table B-4. Date Format Examples
|
Value |
Format |
Result |
|---|---|---|
|
13-Dec-1998 09:13:59 PM |
dd-mon-yyyy |
13-dec-1998 |
|
13-Dec-1998 09:13:59 PM |
dd-Mon-yyyy |
13-Dec-1998 |
|
13-Dec-1998 09:13:59 PM |
DD-MON-YYYY |
13-DEC-1998 |
|
13-Dec-1998 09:13:59 PM |
Month dd, yyyy |
December 13, 1998 |
|
13-Dec-1998 09:13:59 PM |
Month dd, yyyy “at” hh:mi am |
December 13, 1998 at 09:13 pm |
|
13-Dec-1998 09:13:59 PM |
mm/dd/yy |
12/13/98 |
|
13-Dec-1998 09:13:59 PM |
mm/dd/rr |
12/13/98 |
|
13-Dec-1998 09:13:59 PM |
mm/dd/yyyy |
12/13/1998 |
|
13-Dec-1998 09:13:59 PM |
Day |
Sunday |
|
13-Dec-1998 09:13:59 PM |
ddd |
347 |
|
13-Dec-1998 09:13:59 PM |
ww |
50 |
|
13-Dec-1998 09:13:59 PM |
q |
4 |
|
13-Dec-1998 09:13:59 PM |
year |
nineteen ninety-eight |
|
13-Dec-1998 09:13:59 PM |
Year |
Nineteen Ninety-Eight |
|
13-Dec-1998 09:13:59 PM |