6.5 Date and Time Formats

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.

6.5.1 AD/BC Indicators

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.

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

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

6.5.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 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

6.5.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

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

  
  

SELECT TO_CHAR(SYSDATE, 'HH:MM:SS') FROM DUAL;



TO_CHAR(

--------

02:10:32

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;



TO_CHAR(

--------

02:57:21

These rules apply to all text elements, such as those used to represent month names, day names, and so forth.

6.5.4 Two-Digit Years

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.

6.5.5 ISO Standard Issues

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:

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.5.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 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.

6.5.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.

6.5.6 Database Parameters

The default formats to use when converting temporal data to character form are determined by database parameters. The key parameters are:

NLS_DATE_FORMAT

Specifies the default format used by TO_DATE and TO_CHAR functions when converting character data into data of type DATE or vice versa.

NLS_TIMESTAMP_FORMAT

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.

NLS_TIMESTAMP_TZ_FORMAT

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 NLS_LANG environment variable must be set for any other NLS_ environment variable setting to take effect. Unless NLS_LANG is set, all settings for other NLS_ environment variables are ignored.

The session-level setting overrides the environment variable setting, and the environment variable setting overrides the initialization parameter setting.