In this topic we apply various Date Format Elements to some test dates and see what output they produce. The "Output" column will contain the results of a function call like
TO_CHAR( (Input), '(Format Element)' )
Format elements in this topic that refer to an "ISO" standard follow the ISO 8601 standard for date and time representations.
Globalized Date Formats
The following two date format elements will display dates in whatever format is standard for the current combination of the NLS_LANGUAGE and NLS_TERRITORY session parameters. See the entries for DL and DS at Punctuation and Character Literals in Datetime Format Models for more details.
Format Element Note ------- --------------------------------------------------------------------------- DL Date - long format DS Date - short format
Here is some sample output from these format elements.
alter session set NLS_LANGUAGE = 'GERMAN' ; alter session set NLS_TERRITORY = 'GERMANY' ;
Format Element Input Output ------- ----------- ---------- DS 31.10.00 31.10.2400 DS 28.02.00 28.02.0500 DS 31.12.01 31.12.0001 DS 01.01.00 01.01.0000 DS 21.06.01 21.06.0001 DS 23.11.20 23.11.0820 DS 16.12.47 16.12.1947 DS 15.06.07 15.06.2007 |
Format Element Input Output ------- ----------- ------------------------------ DL 31.10.00 Mittwoch, 31. Oktober 2400 DL 28.02.00 Donnerstag, 28. Februar 500 DL 31.12.01 Mittwoch, 31. Dezember 1 DL 01.01.00 Donnerstag, 1. Januar 0 DL 21.06.01 Dienstag, 21. Juni 1 DL 23.11.20 Freitag, 23. November 820 DL 16.12.47 Dienstag, 16. Dezember 1947 DL 15.06.07 Freitag, 15. Juni 2007 |
alter session set NLS_LANGUAGE = 'AMERICAN' ; alter session set NLS_TERRITORY = 'AMERICA' ;
Format Element Input Output ------- ----------- ---------- DS 31-OCT-00 10/31/2400 DS 28-FEB-00 2/28/500 DS 31-DEC-01 12/31/1 DS 01-JAN-00 1/1/0 DS 21-JUN-01 6/21/1 DS 23-NOV-20 11/23/820 DS 16-DEC-47 12/16/1947 DS 15-JUN-07 6/15/2007 |
Format Element Input Output ------- ----------- ------------------------------ DL 31-OCT-00 Wednesday, October 31, 2400 DL 28-FEB-00 Thursday, February 28, 0500 DL 31-DEC-01 Wednesday, December 31, 0001 DL 01-JAN-00 Thursday, January 01, 0000 DL 21-JUN-01 Tuesday, June 21, 0001 DL 23-NOV-20 Friday, November 23, 0820 DL 16-DEC-47 Tuesday, December 16, 1947 DL 15-JUN-07 Friday, June 15, 2007 |
Changing NLS_LANGUAGE and NLS_TERRITORY also affects the NLS_DATE_FORMAT and NLS_TIMESTAMP_% session parameters. Before proceeding we will reset their values back to those specified in the Setup topic for this section.
alter session set NLS_DATE_FORMAT = 'SYYYY-MM-DD' ; alter session set NLS_TIMESTAMP_FORMAT = 'SYYYY-MM-DD HH24:MI:SS' ; alter session set NLS_TIMESTAMP_TZ_FORMAT = 'SYYYY-MM-DD HH24:MI:SS TZH:TZM' ;
Epoch Indicators
The epoch format elements are:
Format
Element Note
------- ---------------------------------------------------------------------------
A.D. returns either A.D. or B.C.
AD returns either AD or BC
B.C. returns either A.D. or B.C.
BC returns either AD or BC
E era name - abbrev. (Japanese Imperial, ROC Official, and Thai Buddha
calendars)
EE era name - full (Japanese Imperial, ROC Official, and Thai Buddha
calendars)
Here is some sample output from these format elements.
Format Element Input Output ------- ----------- ---------- A.D. -2400-10-31 B.C. A.D. -0500-02-28 B.C. A.D. -0001-12-31 B.C. A.D. 0000-01-01 A.D. A.D. 0001-06-21 A.D. A.D. 0820-11-23 A.D. A.D. 1947-12-16 A.D. A.D. 2007-06-15 A.D. AD -2400-10-31 BC AD -0500-02-28 BC AD -0001-12-31 BC AD 0000-01-01 AD AD 0001-06-21 AD AD 0820-11-23 AD AD 1947-12-16 AD AD 2007-06-15 AD |
Format Element Input Output ------- ----------- ---------- B.C. -2400-10-31 B.C. B.C. -0500-02-28 B.C. B.C. -0001-12-31 B.C. B.C. 0000-01-01 A.D. B.C. 0001-06-21 A.D. B.C. 0820-11-23 A.D. B.C. 1947-12-16 A.D. B.C. 2007-06-15 A.D. BC -2400-10-31 BC BC -0500-02-28 BC BC -0001-12-31 BC BC 0000-01-01 AD BC 0001-06-21 AD BC 0820-11-23 AD BC 1947-12-16 AD BC 2007-06-15 AD |
|
Format Element Input Output ------- ----------- ---------------- E 0022-12-16 S E 0019-06-15 H EE 0022-12-16 昭和 EE 0019-06-15 平成 |
Centuries
The century format elements are:
Format
Element Note
------- ---------------------------------------------------------------------------
CC century number
SCC century number; BC dates start with minus sign ("-"), AD dates start with
space (" ")
For 4-digit years ending in 00 the century number is the first 2 digits of the year. For 4-digit years ending in 01-99 the century number is the first 2 digits of the year plus 1.
Here is some sample output from these format elements.
Format Element Input Output ------- ----------- ---------- CC -2400-10-31 24 CC -0500-02-28 05 CC -0001-12-31 01 CC 0000-01-01 01 CC 0001-06-21 01 CC 0820-11-23 09 CC 1947-12-16 20 CC 2007-06-15 21 |
Format Element Input Output ------- ----------- ---------- SCC -2400-10-31 -24 SCC -0500-02-28 -05 SCC -0001-12-31 -01 SCC 0000-01-01 -01 SCC 0001-06-21 01 SCC 0820-11-23 09 SCC 1947-12-16 20 SCC 2007-06-15 21 |
Years
The year format elements are:
Format Element Note ------- --------------------------------------------------------------------------- Y,YYY year - with comma SYYYY year - all four digits; BC dates indicated with a minus sign syear year, spelled out, lowercase; BC dates indicated with a minus sign SYear year, spelled out, initcap; BC dates indicated with a minus sign SYEAR year, spelled out, uppercase; BC dates indicated with a minus sign YYYY year - all four digits year year, spelled out, lowercase Year year, spelled out, initcap YEAR year, spelled out, uppercase RRRR year - all four digits IYYY year - all four digits (ISO year) YYY year - last three digits IYY year - last three digits (ISO year) YY year - last two digits RR year - last two digits IY year - last two digits (ISO year) Y year - last digit I year - last digit (ISO year)
Here is some sample output from these format elements.
Format Element Input Output ------- ----------- ---------- Y,YYY -2400-10-31 2,400 Y,YYY -0500-02-28 0,500 Y,YYY -0001-12-31 0,001 Y,YYY 0000-01-01 0,000 Y,YYY 0001-06-21 0,001 Y,YYY 0820-11-23 0,820 Y,YYY 1947-12-16 1,947 Y,YYY 2007-06-15 2,007 SYYYY -2400-10-31 -2400 SYYYY -0500-02-28 -0500 SYYYY -0001-12-31 -0001 SYYYY 0000-01-01 0000 SYYYY 0001-06-21 0001 SYYYY 0820-11-23 0820 SYYYY 1947-12-16 1947 SYYYY 2007-06-15 2007 YYYY -2400-10-31 2400 YYYY -0500-02-28 0500 YYYY -0001-12-31 0001 YYYY 0000-01-01 0000 YYYY 0001-06-21 0001 YYYY 0820-11-23 0820 YYYY 1947-12-16 1947 YYYY 2007-06-15 2007 RRRR -2400-10-31 2400 RRRR -0500-02-28 0500 RRRR -0001-12-31 0001 RRRR 0000-01-01 0000 RRRR 0001-06-21 0001 RRRR 0820-11-23 0820 RRRR 1947-12-16 1947 RRRR 2007-06-15 2007 Format Element Input Output ------- ----------- ---------- IYYY -2400-10-31 2400 IYYY -0500-02-28 0500 IYYY -0001-12-31 0000 IYYY 0000-01-01 0000 IYYY 0001-06-21 0001 IYYY 0820-11-23 0820 IYYY 1947-12-16 1947 IYYY 2007-06-15 2007 YYY -2400-10-31 400 YYY -0500-02-28 500 YYY -0001-12-31 001 YYY 0000-01-01 000 YYY 0001-06-21 001 YYY 0820-11-23 820 YYY 1947-12-16 947 YYY 2007-06-15 007 IYY -2400-10-31 400 IYY -0500-02-28 500 IYY -0001-12-31 000 IYY 0000-01-01 000 IYY 0001-06-21 001 IYY 0820-11-23 820 IYY 1947-12-16 947 IYY 2007-06-15 007 YY -2400-10-31 00 YY -0500-02-28 00 YY -0001-12-31 01 YY 0000-01-01 00 YY 0001-06-21 01 YY 0820-11-23 20 YY 1947-12-16 47 YY 2007-06-15 07 Format Element Input Output ------- ----------- ---------- RR -2400-10-31 00 RR -0500-02-28 00 RR -0001-12-31 01 RR 0000-01-01 00 RR 0001-06-21 01 RR 0820-11-23 20 RR 1947-12-16 47 RR 2007-06-15 07 IY -2400-10-31 00 IY -0500-02-28 00 IY -0001-12-31 00 IY 0000-01-01 00 IY 0001-06-21 01 IY 0820-11-23 20 IY 1947-12-16 47 IY 2007-06-15 07 Y -2400-10-31 0 Y -0500-02-28 0 Y -0001-12-31 1 Y 0000-01-01 0 Y 0001-06-21 1 Y 0820-11-23 0 Y 1947-12-16 7 Y 2007-06-15 7 I -2400-10-31 0 I -0500-02-28 0 I -0001-12-31 0 I 0000-01-01 0 I 0001-06-21 1 I 0820-11-23 0 I 1947-12-16 7 I 2007-06-15 7 |
Format Element Input Output ------- ----------- ------------------------------ syear -2400-10-31 -two thousand four hundred syear -0500-02-28 -five hundred syear -0001-12-31 -one syear 0000-01-01 zero syear 0001-06-21 one syear 0820-11-23 eight twenty syear 1947-12-16 nineteen forty-seven syear 2007-06-15 two thousand seven SYear -2400-10-31 -TWO THOUSAND FOUR HUNDRED SYear -0500-02-28 -FIVE HUNDRED SYear -0001-12-31 -ONE SYear 0000-01-01 ZERO SYear 0001-06-21 ONE SYear 0820-11-23 EIGHT TWENTY SYear 1947-12-16 NINETEEN FORTY-SEVEN SYear 2007-06-15 TWO THOUSAND SEVEN SYEAR -2400-10-31 -TWO THOUSAND FOUR HUNDRED SYEAR -0500-02-28 -FIVE HUNDRED SYEAR -0001-12-31 -ONE SYEAR 0000-01-01 ZERO SYEAR 0001-06-21 ONE SYEAR 0820-11-23 EIGHT TWENTY SYEAR 1947-12-16 NINETEEN FORTY-SEVEN SYEAR 2007-06-15 TWO THOUSAND SEVEN year -2400-10-31 two thousand four hundred year -0500-02-28 five hundred year -0001-12-31 one year 0000-01-01 zero year 0001-06-21 one year 0820-11-23 eight twenty year 1947-12-16 nineteen forty-seven year 2007-06-15 two thousand seven Format Element Input Output ------- ----------- ------------------------------ Year -2400-10-31 Two Thousand Four Hundred Year -0500-02-28 Five Hundred Year -0001-12-31 One Year 0000-01-01 Zero Year 0001-06-21 One Year 0820-11-23 Eight Twenty Year 1947-12-16 Nineteen Forty-Seven Year 2007-06-15 Two Thousand Seven YEAR -2400-10-31 TWO THOUSAND FOUR HUNDRED YEAR -0500-02-28 FIVE HUNDRED YEAR -0001-12-31 ONE YEAR 0000-01-01 ZERO YEAR 0001-06-21 ONE YEAR 0820-11-23 EIGHT TWENTY YEAR 1947-12-16 NINETEEN FORTY-SEVEN YEAR 2007-06-15 TWO THOUSAND SEVEN |
See Calculating Week Numbers for information on how year numbers are calculated.
Quarters
The format elements for the four quarters in a year are:
Format Element Note ------- --------------------------------------------------------------------------- Q quarter - 1-4 (Jan-Mar = 1)
Here is some sample output from this format elements.
Format Element Input Output ------- ----------- ---------- Q -2400-10-31 4 Q -0500-02-28 1 Q -0001-12-31 4 Q 0000-01-01 1 Q 0001-06-21 2 Q 0820-11-23 4 Q 1947-12-16 4 Q 2007-06-15 2
Months
The format elements for months are:
Format Element Note ------- --------------------------------------------------------------------------- RM month number - I-XII (in Roman numerals) MM month number - 01-12 (Jan = 01) MONTH month name - full MON month name - abbreviated
Here is some sample output from these format elements.
Format Element Input Output ------- ----------- ---------- MM -2400-10-31 10 MM -0500-02-28 02 MM -0001-12-31 12 MM 0000-01-01 01 MM 0001-06-21 06 MM 0820-11-23 11 MM 1947-12-16 12 MM 2007-06-15 06 RM -2400-10-31 X RM -0500-02-28 II RM -0001-12-31 XII RM 0000-01-01 I RM 0001-06-21 VI RM 0820-11-23 XI RM 1947-12-16 XII RM 2007-06-15 VI |
Format Element Input Output ------- ----------- ------------------------------ MON -2400-10-31 OCT MON -0500-02-28 FEB MON -0001-12-31 DEC MON 0000-01-01 JAN MON 0001-06-21 JUN MON 0820-11-23 NOV MON 1947-12-16 DEC MON 2007-06-15 JUN MONTH -2400-10-31 OCTOBER MONTH -0500-02-28 FEBRUARY MONTH -0001-12-31 DECEMBER MONTH 0000-01-01 JANUARY MONTH 0001-06-21 JUNE MONTH 0820-11-23 NOVEMBER MONTH 1947-12-16 DECEMBER MONTH 2007-06-15 JUNE |
Weeks
The week format elements are:
Format Element Note ------- --------------------------------------------------------------------------- WW week number of year - 1-53 (week 1 runs from Jan. 1 to Jan. 7) IW week number of year - 1-52 or 1-53 (based on ISO standard) W week number of month - 1-5 (week 1 runs from day 1 to day 7 of month)
Here is some sample output from these format elements.
Format Element Input Output ------- ----------- ---------- IW -2400-10-31 44 IW -0500-02-28 09 IW -0001-12-31 01 IW 0000-01-01 01 IW 0001-06-21 25 IW 0820-11-23 47 IW 1947-12-16 51 IW 2007-06-15 24 WW -2400-10-31 44 WW -0500-02-28 09 WW -0001-12-31 53 WW 0000-01-01 01 WW 0001-06-21 25 WW 0820-11-23 47 WW 1947-12-16 50 WW 2007-06-15 24 |
Format Element Input Output ------- ----------- ---------- W -2400-10-31 5 W -0500-02-28 4 W -0001-12-31 5 W 0000-01-01 1 W 0001-06-21 3 W 0820-11-23 4 W 1947-12-16 3 W 2007-06-15 3 |
See Calculating Week Numbers for more information on how week numbers are calculated.
Days
The day format elements are:
Format Element Note ------- --------------------------------------------------------------------------- D day number of week - 1-7 (starting day of week depends on NLS_TERRITORY) DD day number of month - 1-31 DDD day number of year - 1-366 J day number since January 1, 4712 BC (Julian day) DAY day name - full DY day name - abbreviated
Here is some sample output from these format elements.
Format Element Input Output ------- ----------- ---------- D -2400-10-31 4 D -0500-02-28 5 D -0001-12-31 4 D 0000-01-01 5 D 0001-06-21 3 D 0820-11-23 6 D 1947-12-16 3 D 2007-06-15 6 DD -2400-10-31 31 DD -0500-02-28 28 DD -0001-12-31 31 DD 0000-01-01 01 DD 0001-06-21 21 DD 0820-11-23 23 DD 1947-12-16 16 DD 2007-06-15 15 DDD -2400-10-31 305 DDD -0500-02-28 059 DDD -0001-12-31 365 DDD 0000-01-01 001 DDD 0001-06-21 172 DDD 0820-11-23 328 DDD 1947-12-16 350 DDD 2007-06-15 166 J -2400-10-31 0844762 J -0500-02-28 1538491 J -0001-12-31 1721057 J 0000-01-01 1721058 J 0001-06-21 1721595 J 0820-11-23 2020890 J 1947-12-16 2432536 J 2007-06-15 2454267 |
Format Element Input Output ------- ----------- ------------------------------ DAY -2400-10-31 WEDNESDAY DAY -0500-02-28 THURSDAY DAY -0001-12-31 WEDNESDAY DAY 0000-01-01 THURSDAY DAY 0001-06-21 TUESDAY DAY 0820-11-23 FRIDAY DAY 1947-12-16 TUESDAY DAY 2007-06-15 FRIDAY DY -2400-10-31 WED DY -0500-02-28 THU DY -0001-12-31 WED DY 0000-01-01 THU DY 0001-06-21 TUE DY 0820-11-23 FRI DY 1947-12-16 TUE DY 2007-06-15 FRI |
Note the result of the 'D' format element is NLS_TERRITORY dependent.
alter session set NLS_TERRITORY = 'GERMANY' ; select to_char( date '2007-07-01', 'Day' ) as day_name , to_char( date '2007-07-01', 'D' ) as day_number from dual ;
DAY_NAME D --------- - Sunday 7
alter session set NLS_TERRITORY = 'AMERICA' ; select to_char( date '2007-07-01', 'Day' ) as day_name , to_char( date '2007-07-01', 'D' ) as day_number from dual ;
DAY_NAME D --------- - Sunday 1
Changing NLS_LANGUAGE and NLS_TERRITORY also affects the NLS_DATE_FORMAT and NLS_TIMESTAMP_% session parameters. Before proceeding we will reset their values back to those specified in the Setup topic for this section.
alter session set NLS_DATE_FORMAT = 'SYYYY-MM-DD' ; alter session set NLS_TIMESTAMP_FORMAT = 'SYYYY-MM-DD HH24:MI:SS' ; alter session set NLS_TIMESTAMP_TZ_FORMAT = 'SYYYY-MM-DD HH24:MI:SS TZH:TZM' ;