Section 6.2. Getting Dates In and Out of a Database

6.2 Getting Dates In and Out of a Database In the real world, dates are not always represented using Oracle's DATE datatype. At various times, we'll need to convert DATEs to other datatypes and vice versa. This is particularly true when we interface an Oracle database with an external system, for example when we are accepting date input from an external system in which dates are represented as strings of characters (or even as numbers), or when we are sending output from an Oracle database to another application that doesn't understand Oracle's DATE datatype. We also need to convert DATE values to text when we display dates on a screen or generate a printed report. Oracle provides two extremely useful functions to convert dates: TO_DATE TO_CHAR As their names suggest, TO_DATE is used to convert character data, or numeric data, into a DATE value, and TO_CHAR is used to convert a DATE value into a string of characters. Date formats, discussed later in this section, come in particularly handy for such conversions. 6.2.1 TO_DATE TO_DATE is a built-in SQL function that converts a character string into a date. Input to the TO_DATE function can be a string literal, a PL/SQL variable, or a database column of the CHAR or VARCHAR2 datatype. Call TO_DATE as follows: TO_DATE(string [,format]) The syntax elements are: string Specifies a string literal, a PL/SQL variable, or a database column containing character data (or even numeric data) convertible to a date. format Specifies the format of the input string. The format must be a valid combination of format codes shown later in this chapter in Section 6.2.3. Specifying a date format is optional. When we don't specify a format, the input string is assumed to be in the default date format (specified by the NLS_DATE_FORMAT parameter setting). We can convert a number to a DATE using TO_DATE. When we supply a number to the TO_DATE function, Oracle implicitly converts the input number into a string, and then the resulting string gets passed as input to TO_DATE. 6.2.1.1 Using the default date format Every Oracle database has a default date format. If our DBA has not specified anything different, the default date format is as follows: DD-MON-YY When we invoke TO_DATE without explicitly specifying a date format, Oracle expects our input string to be in the default date format. The following INSERT statement converts a string in the default date format into a date, which is then inserted into the EMPLOYEE table: INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE('22-OCT-99')); 1 row created. SELECT * FROM EMPLOYEE; EMP_ID FNAME LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE ------- -------- ------- ---------- -------------- ---------- --------- 2304 John Smith 20 1258 20000 22-OCT-99 Note the HIRE_DATE column is a DATE field, and the character string '22-OCT-99' was converted to a date by the TO_DATE function. We don't need the format in this case, because the supplied string is in the default date format. In fact, since the supplied string is in the default date format, we don't even need the TO_DATE function. Oracle automatically performs an implicit type conversion, as in this example: INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, '22-OCT-99'); 1 row created. Even though Oracle provides means for implicit datatype conversions, we recommend always using explicit conversions, because implicit conversions are not obvious and may lead to confusion. They may also suddenly fail should a DBA change the database's default date format. 6.2.1.2 Specifying a date format If we wish to specify a date format, there are at least two approaches we can take: Specify the format at the session level, in which case it applies to all implicit conversions, and to all TO_DATE conversions for which we do not explicitly specify some other format. Specify the format as a parameter to a TO_DATE call. The following example changes the default date format for the session, and then uses TO_DATE to convert a number to date. ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYY'; Session altered. INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE(102299)); 1 row created. Since the default date format has been changed prior to the conversion, the conversion function TO_DATE doesn't need the date format as an input parameter. While it is possible to pass a number such as 102299 to the TO_DATE function, relying on Oracle's implicit conversion to change the number to a string, and then into a date, it's probably best to pass a string as input to the TO_DATE function. If we attempt this insert without setting the default date format to match the format of the date in the input string, we get an error when Oracle tries to convert the date: ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY'; Session altered. INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE('102299')); (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE('102299')) * ERROR at line 4: ORA-01861: literal does not match format string In such situations, if we do not wish to change our session's default date format, we must specify the date format as the second input parameter to the TO_DATE function: ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY'; Session altered. INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE('102299','MMDDYY')); 1 row created. SELECT * FROM EMPLOYEE; EMP_ID FNAME LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE ------- ------- ------- ---------- -------------- ---------- --------- 2304 John Smith 20 1258 20000 22-OCT-99 Note how TO_DATE interprets the string '102299' as being in the format 'MMDDYY'. Also note that in the result of the SELECT, the date is displayed using the default date format of the session, not the format in which it was inserted. Let's look at one more example to see how a database character column can be converted to a DATE. Let's assume that the REPORT_ID column in the REPORT table actually stores the date on which the report was generated, and that the date is in the format 'MMDDYYYY'. Now, we can use TO_DATE on that column to display the date on which the report was generated: SELECT SENT_TO, REPORT_ID, TO_DATE(REPORT_ID,'MMDDYYYY') DATE_GENERATED FROM REPORT; SENT_TO REPORT_I DATE_GENE ---------------------------- --------- Manager 01011999 01-JAN-99 Director 01121999 12-JAN-99 Vice President 01231999 23-JAN-99 In this example, the TO_DATE function converts the MMDDYYYY data in the column to a date. That date is then implicitly converted into a character string for display purposes, using the default date format. 6.2.2 TO_CHAR The TO_CHAR function is the opposite of the TO_DATE function, and converts a date into a string of characters. Call TO_CHAR as follows: TO_CHAR(date [,format]) The syntax elements are: date Specifies a PL/SQL variable or a database column of the DATE datatype. format Specifies the desired format of the output string. The format must be a valid combination of date format elements as described later in Section 6.2.3. The format is optional. When the format is not specified, the date is output in the default date format (as specified by NLS_DATE_FORMAT). The following example uses TO_CHAR to convert an input date into a string using the default date format: SELECT FNAME, TO_CHAR(HIRE_DATE) FROM EMPLOYEE; FNAME TO_CHAR(H -------------------- --------- John 22-OCT-99 The following example uses TO_CHAR to convert a date into a string, and explicitly specifies a date format: SELECT FNAME, TO_CHAR(HIRE_DATE,'MM/DD/YY') FROM EMPLOYEE; FNAME TO_CHAR( -------------------- -------- John 10/22/99 There are situations when we may need to combine TO_CHAR with TO_DATE. For example, if we want to know on what day of the week January 1, 2000 fell, we can use the following query: SELECT TO_CHAR(TO_DATE('01-JAN-2000','DD-MON-YYYY'),'Day') FROM DUAL; TO_CHAR(T --------- Saturday In this example, the input string '01-JAN-2000' is first converted into a date and then the TO_CHAR function is used to convert this date into a string representing the day of the week. 6.2.3 Date Formats We can display dates in a number of ways. Every country, every industry has its own standard of displaying dates. Oracle provides us with date format codes so that we can interpret and display dates in a wide variety of date 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-YY 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 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. We can use ALTER SYSTEM or ALTER SESSION commands to change the default date format for the instance or the session respectively. Let's take another example to see how we can display a date in a format other than the default format: SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL; TO_CHAR(SY ---------- 10/03/2001 The example converts the date into the format 'MM/DD/YYYY' with the TO_CHAR function. There are many ways to represent a date. These vary from country to country, from industry to industry, and from application to application. Table 6-1 describes the various date formats. Most of the examples in Table 6-1 are based on 03-OCT-2001 03:34:48 PM. 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. Table 6-1. Oracle date format codes Component Options Example     Format Date Punctuation -/,;:.*Space"Text" DD-MON-YYDD MM YYYYDD "of" Month 03-OCT-0103 10 200103 of October Day DD(Day of the month) MM/DD/YY 10/03/01   DDD(Day of the year) DDD/YY 276/01   D(Day of the week) D MM/YY 4 10/01   DAY(Name of the day) DAY MM/YY WEDNESDAY 10/01   day(Name of the day, in lower case) 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) DY MM/YY WED 10/01   Dy(Abbreviated name of the day) Dy MM/YY Wed 10/01 Month MM(Two digit month) MM/DD/YY 10/03/01   MONTH(Name of the month, in upper case) MONTH YY OCTOBER 01   Month(Name of the month, in mixed case) Month YY October 01   MON(Abbreviated name of the month) MON YY OCT 01   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 digit 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) 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   Y,YYY(Year with comma) MM Y,YYY 10 2,001   RR(Round Year depending upon the current year) DD-MON-RR 03-OCT-01   RRRR(Round Year depending upon 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 digit 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 Week 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) HH 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(Ordinal Number) DDTH 03RD   SP(Spelled Number) MMSP TEN   SPTH(Spelled Ordinal Number) DDSPTH THIRD   THSP(Spelled Ordinal Number) DDTHSP THIRD 6.2.3.1 AD/BC indicators Oracle provides two formats, AD and BC, to characterize a year (two more with dots�A.D., B.C.). However, they both serve the same purpose, and we can use either of them with equivalent results. If we have used the format BC in our query, and the date we 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. 6.2.3.2 AM/PM indicators The AM/PM indicators (as well as A.M. and P.M.) behave exactly the same as the AD/BC indicators. If we have used the AM format in our query, and the time we 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 MINUTES: MI or MM Many 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. 6.2.3.3 Case-sensitivity of formats 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 Note that the format HH:MI is case-insensitive�no matter which case we use for the format, the output is the same. The same applies to all other formats 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 mask 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 mask 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 These rules apply to all text elements, such as those used to represent month names, day names, and so forth. 6.2.3.4 Two-digit years Even though Oracle stores the century of the year internally, it allows us to use two-digit years. Therefore, it is important to know how the century is handled when we use a two-digit year. Oracle provides two two-digit year formats that we can use: YY and RR. With the YY year format, the first two digits are assumed to be the current date: 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 this was written, the first two digits of the years 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 1 greater than the first two digits of the current date. If the specified year is greater than 50, and the last two digits of the current year are less than 50, then first two digits of the return date are 1 less than the first two digits of the current date. If the specified year is greater than 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 treats 10-DEC-01 as 10-DEC-2001. Compare this output to the rules we just listed. The year format RRRR (four Rs) allows us to enter either a two-digit year or a four-digit year. If we enter a four-digit year, Oracle behaves as if the year format was YYYY. If we 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. 6.2.4 Date Literals DATE literals are specified in the ANSI standard as a way of representing date constants, and take the following form: DATE 'YYYY-MM-DD' Note that the ANSI date literal doesn't contain the time information. We also can't specify a format. If we want to specify a date literal using this ANSI syntax, we must always use the YYYY-MM-DD date format. The following example illustrates the use of a DATE literal in a SQL statement: INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, DATE '1999-10-22'); 1 row created. SELECT * FROM EMPLOYEE; EMP_ID FNAME LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE ------- -------- ------- ---------- -------------- ---------- --------- 2304 John Smith 20 1258 20000 22-OCT-99 In this example, the date literal DATE '1999-10-22' is interpreted as 22-OCT-99. 6.2.5 ISO Standard Issues The ISO 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 1st is a Monday, Tuesday, Wednesday, or a Thursday, then January 1st belongs to the first week of the new ISO year. The first day of the ISO year is either January 1st (if it is a Monday) or the previous Monday (which actually goes back to the last calendar year). For example, if January 1st is a Tuesday, then the first day of the ISO year is Monday, December 31, of the prior calendar year. If January 1st is a Friday, Saturday, or a Sunday, then January 1st 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 1st. 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 we need to work with ISO dates, Oracle provides date formats that treat ISO years differently from calendar years. These ISO formats are: IW Represents the week of the year in ISO standard. I, IY, IYY and IYYY Represents the ISO year. The following sections describe ISO weeks and years with examples. 6.2.5.1 ISO standard weeks 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 1st. 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 we compute the week number of any date in the year 2001, the results will be the same whether we 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 1st January of 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 4th January, as per the ISO standard. 6.2.5.2 ISO standard year 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.