You can display dates and times in a number of ways. Every country, every industry has its own standard for representing temporal data. Oracle provides you with date and time format codes so that you can interpret and display dates and timestamps in a wide variety of formats.
A simple example of displaying a date is:
SELECT SYSDATE FROM DUAL; SYSDATE --------- 03-OCT-01
By default, the date is displayed using the DD-MON-RR format. This format uses two digits for the date (zero padded on the left), three characters for the month (the first three characters of the English, or your local language, name of the month in uppercase), and two digits for the year of the century (zero padded on the left). The default date format for the database is controlled by the NLS_DATE_FORMAT initialization parameter. You can use ALTER SYSTEM or ALTER SESSION commands to change the default date format for the instance or the session, respectively. You can also use the TO_CHAR function to specify a format on a per-call basis:
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL; TO_CHAR(SY ---------- 10/03/2001
This example converts the current date into the format MM/DD/YYYY using the TO_CHAR function. The second argument is a format string specifying how we want the date to be presented. Table 6-1 describes the various date format elements at your disposal. Most of the examples in Table 6-1 are based on 03-OCT-2001 03:34:48 P.M. Those that involve B.C. dates use the year 2105 B.C. Those that specifically demonstrate A.M. times are based on 03-OCT-2001 11:00:00 AM.
Component |
Options |
Description |
Format |
Output |
---|---|---|---|---|
Punctuation |
- / , ; : . * |
Simply reproduced in the output |
DD-MON-YY |
03-OCT-01 |
Space |
Simply reproduced in the output |
DD MM YYYY |
03 10 2001 | |
"Text" |
Simply reproduced in the output |
DD "of" Month |
03 of October | |
Day |
DD |
Day of the month |
MM/DD/YY |
10/03/01 |
DDD |
Day of the year; starts with 1st January as 1 |
DDD/YY |
276/01 | |
D |
Day of the week; starts with Sunday as 1 |
D MM/YY |
4 10/01 | |
DAY |
Name of the day, in uppercase |
DAY MM/YY |
WEDNESDAY 10/01 | |
Day |
Name of the day, in mixed case |
Day MM/YY |
Wednesday 10/01 | |
DY |
Abbreviated name of the day, in uppercase |
DY MM/YY |
WED 10/01 | |
Dy |
Abbreviated name of the day, in mixed case |
Dy MM/YY |
Wed 10/01 | |
Month |
MM |
Two-digit month |
MM/DD/YY |
10/03/01 |
MONTH |
Name of the month, in uppercase |
MONTH YY |
OCTOBER 0 | |
Month |
Name of the month, in mixed case |
Month YY |
October 0 | |
MON |
Abbreviated name of the month, in uppercase |
MON YY |
OCT 0 | |
Mon |
Name of the month, in mixed case |
Mon YY |
Oct 01 | |
RM |
Roman-numeral month |
DD-RM-YY |
03-X-01 | |
Year |
Y |
Last one digit of year |
MM Y |
10 1 |
YY |
Last two digits of year |
MM YY |
10 01 | |
YYY |
Last three digits of year |
MM YYY |
10 001 | |
YYYY |
Four digits of year |
MM YYYY |
10 2001 | |
Y,YYY |
Year with comma |
MM Y,YYY |
10 2,001 | |
YEAR |
Year spelled out, in uppercase |
MM YEAR |
10 TWO THOUSAND ONE | |
Year |
Year spelled out, in mixed case |
MM Year |
10 Two Thousand One | |
SYYYY |
Four digits of year with "-" sign for BC |
SYYYY |
-2105 | |
RR |
Round year depending on the current year |
DD-MON-RR |
03-OCT-01 | |
RRRR |
Round year depending on the current year |
DD-MON-RRRR |
03-OCT-2001 | |
I |
Last one digit of the ISO Standard year |
MM I |
10 1 | |
IY |
Last two digits of the ISO Standard year |
MM IY |
10 01 | |
IYY |
Last three digits of the ISO Standard year |
MM IYY |
10 001 | |
IYYY |
Four digits of the ISO Standard year |
MM IYYY |
10 2001 | |
Century |
CC |
Century |
CC |
21 |
SCC |
Century with "-" sign for BC |
SCC |
-22 | |
Wtdeek |
W |
Week of the month |
W |
1 |
WW |
Week of the year |
WW |
40 | |
IW |
Week of the year in ISO standard |
IW |
40 | |
Quarter |
Q |
Quarter of the year |
Q |
4 |
Hour |
HH |
Hour of the day 1-12 |
HH |
03 |
HH12 |
Hour of the day 1-12 |
HH12 |
03 | |
HH24 |
Hour of the day 0-23 |
HH24 |
15 | |
Minute |
MI |
Minute of hour 0-59 |
MI |
34 |
Second |
SS |
Second of minute 0-59 |
SS |
48 |
SSSSS |
Seconds past midnight |
SSSSS |
42098 | |
AM/PM |
AM |
Meridian indicator |
HH:MI AM |
11:00 AM |
A.M. |
Meridian indicator with dots |
HH:MI A.M. |
11:00 A.M. | |
PM |
Meridian indicator |
HH:MI PM |
03:34 PM | |
P.M. |
Meridian indicator with dots |
HH:MI P.M. |
03:34 P.M. | |
AD/BC |
AD |
AD indicator |
YY AD |
01 AD |
A.D. |
AD indicator with dots |
YY A.D. |
01 A.D. | |
BC |
BC indicator |
YY BC |
05 BC | |
B.C. |
BC indicator with dots |
YY B.C. |
05 B.C. | |
Julian day |
J |
Number of days since January 1, 4712 BC |
J |
2452186 |
Suffix |
TH or th |
Ordinal number |
DDTH or DDth |
03RD |
SP or sp |
Spelled number |
MMSP or MMsp |
TEN | |
SPTH |
Spelled ordinal number |
DDSPTH |
THIRD | |
THSP |
Spelled ordinal number |
DD THSP |
THIRD | |
Fractional seconds |
FF |
Always use FF, with two Fs. |
HH:MI:SS.FF or HH:MI:SSXFF |
11:47:26.336000 |
Time zone |
TZH |
Time zone hour |
HH:MI:SS.FF TZH |
08:23:46.368 -10 |
TZ |
Time zone minute |
HH:MI:SS:FF TZH:TZM |
08:23:46.368 -10:30 |
Oracle provides two formats, AD and BC (two more with dots?A.D., B.C.), to characterize a year with respect to the year 0. However, they both serve the same purpose, and you can use either of them with equivalent results. If you have used the format BC in a query, and the date you are applying this format to comes out to be an AD year, Oracle is intelligent enough to print AD instead of BC, and vice versa. For example:
SELECT TO_CHAR(SYSDATE, 'YYYY AD'), TO_CHAR(SYSDATE, 'YYYY BC') FROM DUAL; TO_CHAR( TO_CHAR( -------- -------- 2001 AD 2001 AD SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-50000), 'YYYY AD'), TO_CHAR(ADD_MONTHS(SYSDATE,-50000), 'YYYY BC') FROM DUAL; TO_CHAR( TO_CHAR( -------- -------- 2165 BC 2165 BC
In the first example, even though we supplied the BC format with the SYSDATE, it printed 2001 AD in the output, and in the second example, even though we supplied AD with a date 50,000 months earlier (in the BC), it printed BC in the output. The function ADD_MONTHS is discussed later in the chapter.
The Year 0At our graduation party in 1990, all of our classmates agreed to meet after 10 years on January 1, 2000. Some even suggested that since it would be the start of the new Millennium, it would be a great idea to have a millennium get-together. Ten years passed by. Some of us, who had kept in touch after graduation, remembered our millennium plan and decided to have a get-together on January 1, 2000. The party was well organized, and everyone was having a great time, untill someone came up with the thought that our party wasn't actually a millennium party. It was one year earlier than the actual start of the millennium. There was no year "0," and therefore, the year 2000 was the last year of the then current millennium. The new millennium actually began a year later January 1, 2001. Debate over this issue continued till the wee hours in the morning, and four years later we still debate it. When the millennium began is an interesting topic for discussion, and the basis for that discussion lies in the convention we use in numbering our years. The common convention is the BC/AD convention, in which the sequence of years is ..., 2 BC, 1 BC, 1 AD, 2 AD, .... In this convention, there is no year 0?1 AD comes right after 1 BC. However, the convention used by astronomers includes a year 0. And, instead of representing BC and AD, astronomers prefer using the "-" and "+" notation. In the astronomical convention, the sequence of years is ..., -1, 0, +1, +2, .... Oracle uses the BC/AD convention, and doesn't allow the year 0, as shown in the following example: SELECT TO_DATE('0000-12-10','YYYY-MM-DD') FROM DUAL; SELECT TO_DATE('0000-12-10','YYYY-MM-DD') FROM DUAL * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 Since there is no year 0 in the Oracle calendar, the year after 1 BC must be the year 1 AD. Therefore, the difference between the date "January 1, 0001 AD" and the date "December 31, 0001 BC" should be 1 day. However, the following example is in complete contrast to this: SELECT TO_DATE('0001-01-01 AD','YYYY-MM-DD AD') - TO_DATE('0001-12-31 BC', 'YYYY-MM-DD BC') FROM DUAL; TO_DATE('0001-01-01AD','YYYY- ----------------------------- 367 Where does 367 come from? You were expecting 1, right? 367 is the result of 366 (the number of days in the year 0, being a leap year) plus 1. The existence of year 0 indicates that Oracle's date arithmetic uses the astronomical convention. This contradiction is known as Oracle's year-zero bug. Refer to an enlightening article by Peter Gulutzan and Trudy Pelzer at http://www.orafaq.net/papers/dates_o.doc for details on this and other interesting facts involving Oracle's calendar. |
The AM/PM indicators (as well as A.M. and P.M.) behave exactly the same as the AD/BC indicators. If you have used the AM format in a query, and the time you are applying this format to comes out to be a PM time, Oracle is intelligent enough to print PM instead of AM, and vice versa. For example:
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS AM'), TO_CHAR(SYSDATE, 'HH:MI:SS PM'), TO_CHAR(SYSDATE - 8/24, 'HH:MI:SS AM'), TO_CHAR(SYSDATE - 8/24, 'HH:MI:SS PM') FROM DUAL; TO_CHAR(SYS TO_CHAR(SYS TO_CHAR(SYS TO_CHAR(SYS ----------- ----------- ----------- ----------- 06:58:07 PM 06:58:07 PM 10:58:07 AM 10:58:07 AM
Some date formats are case-sensitive while others aren't. The formats that represent numbers are not case-sensitive. For example:
SELECT TO_CHAR(SYSDATE, 'HH:MI') UPPER, TO_CHAR(SYSDATE, 'hh:mi') LOWER, TO_CHAR(SYSDATE, 'Hh:mI') MIXED FROM DUAL; UPPER LOWER MIXED ----- ----- ----- 03:17 03:17 03:17
You can see that the format HH:MI is case-insensitive?no matter which case you use for the format, the output is the same. The same applies to all other format elements that represent numbers, for example, DD, MM, YY, etc.
Date formats that represent textual date components are case-sensitive. For example, the format DAY is different from day. The following rules apply for determining the case of the output when a textual date format is used:
If the first character of the format is lowercase, then the output will be lowercase, regardless of the case of the other characters in the format:
SELECT TO_CHAR(SYSDATE, 'month'), TO_CHAR(SYSDATE, 'mONTH'), TO_CHAR(SYSDATE, 'moNTh') FROM DUAL; TO_CHAR(S TO_CHAR(S TO_CHAR(S --------- --------- --------- october october october
If the first character of the format element is uppercase and the second character is also uppercase, then the output will be uppercase, regardless of the case of the other characters in the format:
SELECT TO_CHAR(SYSDATE, 'MOnth'), TO_CHAR(SYSDATE, 'MONTH') FROM DUAL; TO_CHAR(S TO_CHAR(S --------- --------- OCTOBER OCTOBER
If the first character of the format element is uppercase and the second character is lowercase, then the output will have an uppercase first character and all other characters lowercase, regardless of the case of the other characters in the format:
SELECT TO_CHAR(SYSDATE, 'MoNTH'), TO_CHAR(SYSDATE, 'Month') FROM DUAL; TO_CHAR(S TO_CHAR(S --------- --------- October October
MINUTES: MI or MMMany SQL beginners assume that since HH represents hours and SS represents seconds, MM would represent minutes, and try to write the following SQL queries to print the current time: SELECT TO_CHAR(SYSDATE, 'HH:MM:SS') FROM DUAL; TO_CHAR( -------- 02:10:32 However, this is wrong. MM represents months and not minutes. The format for minutes is MI. Therefore, remember to use MI instead of MM when attempting to get the minutes part of the date. The correct query is: SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; TO_CHAR( -------- 02:57:21 It becomes extremely difficult to debug an application if the MM format is embedded in the code instead of MI. |
These rules apply to all text elements, such as those used to represent month names, day names, and so forth.
Even though Oracle stores the century of the year internally, it allows you to use two-digit years. Therefore, it is important to know how the century is handled when you use a two-digit year. Oracle provides two two-digit year formats that you can use: YY and RR.
With the YY year format, the century is assumed to be the current century:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY'; Session altered. SELECT SYSDATE, TO_CHAR(SYSDATE,'DD-MON-YYYY') FROM DUAL; SYSDATE TO_CHAR(SYS --------- ----------- 06-OCT-01 06-OCT-2001 SELECT TO_CHAR(TO_DATE('10-DEC-99'),'DD-MON-YYYY'), TO_CHAR(TO_DATE('10-DEC-01'),'DD-MON-YYYY') FROM DUAL; TO_CHAR(TO_ TO_CHAR(TO_ ----------- ----------- 10-DEC-2099 10-DEC-2001
Since the current date was 06-OCT-2001 when these examples were executed, the first two digits of the years (the century component) in this example are assumed to be 20.
With the RR year format, the first two digits of the specified year are determined based upon the last two digits of the current year and the last two digits of year specified. The following rules apply:
If the specified year is less than 50, and the last two digits of the current year are less than 50, then the first two digits of the return date are the same as the first two digits of the current date.
If the specified year is less than 50, and the last two digits of the current year are greater than or equal to 50, then first two digits of the return date are one greater than the first two digits of the current date.
If the specified year is greater than or equal to 50, and the last two digits of the current year are less than 50, then first two digits of the return date are one less than the first two digits of the current date.
If the specified year is greater than or equal to 50, and the last two digits of the current year are greater than or equal to 50, then the first two digits of the return date are the same as the first two digits of the current date.
The following example demonstrates these rules:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR'; Session altered. SELECT SYSDATE, TO_CHAR(SYSDATE,'DD-MON-YYYY') FROM DUAL; SYSDATE TO_CHAR(SYS --------- ----------- 06-OCT-01 06-OCT-2001 SELECT TO_CHAR(TO_DATE('10-DEC-99'),'DD-MON-YYYY'), TO_CHAR(TO_DATE('10-DEC-01'),'DD-MON-YYYY') FROM DUAL; TO_CHAR(TO_ TO_CHAR(TO_ ----------- ----------- 10-DEC-1999 10-DEC-2001
The ALTER SESSION command sets the default date format to DD-MON-RR. The next SELECT uses SYSDATE to show the current date at the time the example was executed. The final SELECT demonstrates the use of the RR date format (both TO_DATE calls rely on the default format set earlier). Note that the DD-MON-RR date format treats 10-DEC-99 as 10-DEC-1999, whereas it treats 10-DEC-01 as 10-DEC-2001. Compare this output to the rules we just listed.
The year format RRRR (four Rs) allows you to enter either a two-digit year or a four- digit year. If you enter a four-digit year, Oracle behaves as if the year format was YYYY. If you enter a two-digit year, Oracle behaves as if the year format is RR. The RRRR format is rarely used. Most SQL programmers prefer to use either YYYY, or to explicitly specify RR.
The ISO 8601 standard determines the start date of the first week of the year based upon whether most of the days in the week belong to the new year or to the previous year. If January 1 is a Monday, Tuesday, Wednesday, or a Thursday, then January 1 belongs to the first week of the new ISO year. The first day of the ISO year is either January 1 (if it is a Monday) or the previous Monday (which actually goes back to the last calendar year). For example, if January 1 is a Tuesday, then the first day of the ISO year is Monday, December 31, of the prior calendar year.
If January 1 is a Friday, Saturday, or a Sunday, then January 1 belongs to the last week of the previous ISO year. The first day of the first week of the new ISO year is then considered to be the Monday following January 1. For example, if January 1 falls on a Saturday, then the first day of the ISO year is considered to be Monday, January 3.
If you need to work with ISO dates, Oracle provides date formats that treat ISO years differently from calendar years. These ISO formats are:
Represents the week of the year in ISO standard.
Represents the ISO year.
The following sections describe ISO weeks and years with examples.
In the ISO standard, weeks of the year are counted differently than regular calendar weeks. In a regular calendar, the first week of the year starts on January 1. 01-JAN is the first date of the first week. However, in the ISO standard, a week always starts on a Monday and ends on a Sunday. Therefore, the first date of the first week is considered to be the date of the nearest Monday. This date could be a couple of days later than 01-JAN, or it could be a couple of days earlier (in the previous year).
The format WW returns the week of the year in terms of the regular calendar, and the format IW returns the week of the year in terms of the ISO standard. Since 01- JAN-2001 was a Monday, it was considered the start date of the first week in terms of the regular calendar as well as in terms of the ISO standard. Therefore, if you compute the week number of any date in the year 2001, the results will be the same whether you use the regular calendar or the ISO calendar. For example:
SELECT TO_CHAR(TO_DATE('10-DEC-01'),'WW'), TO_CHAR(TO_DATE('10-DEC-01'),'IW') FROM DUAL; TO TO -- -- 50 50
However, the year 1999 didn't start on a Monday. Therefore, for some dates, the week number in the ISO standard could be different from that of the regular calendar. For example:
SELECT TO_CHAR(TO_DATE('10-DEC-99'),'WW'), TO_CHAR(TO_DATE('10-DEC-99'),'IW') FROM DUAL; TO TO -- -- 50 49
The ISO Standard can cause a year to have 53 weeks. Here's an example:
SELECT TO_CHAR(TO_DATE('01-JAN-99'),'IW'), TO_CHAR(TO_DATE('01-JAN- 99'),'Day') FROM DUAL; TO TO_CHAR(T -- --------- 53 Friday
Note that the ISO standard treats January 1, 1999 to be in the 53rd week of 1998, because it falls on a Friday. The first week of 1999 starts on the subsequent Monday, which is January 4, as per the ISO standard.
The year formats I, IY, IYY, and IYYY represent the ISO year. IYYY represents the four-digit ISO year, IYY represents the last three digits of the ISO year, IY represents the last two digits of the ISO year, and I represents the last digit of the ISO year. Remember that the start date of an ISO year is not necessarily January 1. The following example returns the ISO and calendar years for January 1, 1999:
SELECT TO_CHAR(TO_DATE('01-JAN-99'),'IYYY'), TO_CHAR(TO_DATE('01-JAN-99'),'YYYY') FROM DUAL; TO_C TO_C ---- ---- 1998 1999
Notice that even though the calendar year is 1999, the ISO year is considered to be 1998. That's because 01-Jan-1999 fell on a Friday?late in the week, which causes the week to be considered part of the previous ISO year. The following example demonstrates the opposite situation:
SELECT TO_CHAR(TO_DATE('31-DEC-90'),'IYYY'), TO_CHAR(TO_DATE('31-DEC-90'),'YYYY') FROM DUAL; TO_C TO_C ---- ---- 1991 1990
This time, the calendar year is 1990, but the date 31-Dec-1990 is considered to be in ISO year 1991. This is because 01-Jan-1991 fell on a Tuesday, early enough in the week for the entire week to be considered part of the next ISO year.
The default formats to use when converting temporal data to character form are determined by database parameters. The key parameters are:
Specifies the default format used by TO_DATE and TO_CHAR functions when converting character data into data of type DATE or vice versa.
Specifies the default format used by TO_TIMESTAMP and TO_CHAR functions when converting character data into data of type TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE or vice versa.
Specifies the default format used by TO_TIMESTAMP_TZ and TO_CHAR when converting character data into data of type TIMESTAMP WITH TIME ZONE or vice versa.
If any of these parameters are not set explicitly, its default value is derived from the setting for the NLS_TERRITORY parameter. The NLS_TERRITORY parameter specifies the territory, such as "AMERICA" or "CZECH REPUBLIC." For more details on the NLS parameters, refer to Oracle's Globalization Support Guide.
You or your DBA can specify values for these default date format parameters in one of the following three ways:
By specifying a value in the instance's initialization parameter file. For example:
NLS_DATE_FORMAT = 'YYYY-MM-DD'
A format string specified using this approach becomes the instance-wide default.
By issuing an ALTER SESSION command to change the default for your current session:
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
By setting an environment variable on your client, to change the default value for all sessions initiated from your client:
setenv NLS_DATE_FORMAT 'YYYY-MM-DD'
|
The session-level setting overrides the environment variable setting, and the environment variable setting overrides the initialization parameter setting.