The time zone of a database is usually set at the time of creation of the database. Alternatively, a database administrator can change the time zone using the ALTER DATABASE command, after a database is created. Both CREATE DATABASE and ALTER DATABASE commands take an optional SET TIME_ZONE clause:

SET TIME_ZONE = '+ | - HH:MI' | 'time_zone_region'

You can specify a time zone in one of two ways:

The following examples use the SET TIME_ZONE clause to set the time zone of a database:

CREATE DATABASE  . . .  SET TIME_ZONE = '-05:00';

ALTER DATABASE  . . .  SET TIME_ZONE = 'EST';

Both of these examples set the time zone to Eastern Standard Time. The first example uses a displacement (-05:00) from UTC. The second example uses the time zone abbreviation (EST). EST is 5 hours behind UTC, and is therefore equivalent to “-5:00”.

Each session can have a time zone as well. You can set the time zone of a session by using the ALTER SESSION SET TIME_ZONE statement. The syntax for the SET TIME_ZONE clause in the ALTER SESSION statement is the same as that in the CREATE DATABASE and ALTER DATABASE statements.

The following example shows two ways to set the time zone of a session to Pacific Standard Time:

ALTER SESSION SET TIME_ZONE = '-08:00';

ALTER SESSION SET TIME_ZONE = 'PST';

To set the session time zone to the local operating system time zone (e.g., the time zone of a PC initiating a remote user session), you can use the LOCAL keyword in the SET TIME_ZONE clause, as in the following example:

ALTER SESSION SET TIME_ZONE = LOCAL;

To set the session time zone to the database time zone, use the DBTIMEZONE keyword in the SET TIME_ZONE clause, as in the following example:

ALTER SESSION SET TIME_ZONE = DBTIMEZONE;

Oracle’s DATE data type holds date as well as time information. Regardless of the date format you use for display purposes, Oracle stores dates internally in one standard format. Internal to the database, a date is a fixed-length, 7-byte field. The seven bytes represent the following pieces of information:

Even though the data type is called a DATE, it also stores the time. You choose the components to display (the date, the time, the date and the time, etc.) when you retrieve a DATE value from the database. Or, if you are putting a DATE value into a program (e.g., a Java program) you might choose to extract the components of interest after transferring the entire date/time value to that program.

To provide support for fractional seconds along with date and time data, and also to provide support for time zones, Oracle9i introduced the following temporal data types:

TIMESTAMP [ (precision for fractional seconds) ]

                  CREATE TABLE transaction (
                  transaction_id NUMBER(10),
                  transaction_timestamp TIMESTAMP,
                  status VARCHAR2(12));

Table created.

DESC transaction

 Name                        Null?    Type
 --------------------------- -------- ---------------
 TRANSACTION_ID                       NUMBER(10)
 TRANSACTION_TIMESTAMP                TIMESTAMP(6)
 STATUS                               VARCHAR2(12)

TIMESTAMP [ (precision for fractional seconds) ] WITH TIME ZONE

                  CREATE TABLE transaction_time_zone (
                  transaction_id NUMBER(10),
                  transaction_timestamp TIMESTAMP(3) WITH TIME ZONE,
                  status VARCHAR2(12));

Table created.

DESC transaction_time_zone

 Name                            Null?    Type
 ------------------------------- -------- ------------------------
 TRANSACTION_ID                           NUMBER(10)
 TRANSACTION_TIMESTAMP                    TIMESTAMP(3) WITH TIME ZONE
 STATUS                                   VARCHAR2(12)

TIMESTAMP [ (precision for fractional seconds) ] WITH LOCAL TIME ZONE

                  CREATE TABLE transaction_local_time_zone (
                  transaction_id NUMBER(10),
                  transaction_timestamp TIMESTAMP(3) WITH LOCAL TIME ZONE,
                  status VARCHAR2(12));

Table created.

DESC transaction_local_time_zone

 Name                     Null?    Type
 ------------------------ -------- ------------------------
 TRANSACTION_ID                    NUMBER(10)
 TRANSACTION_TIMESTAMP             TIMESTAMP(3) WITH LOCAL TIME ZONE
 STATUS                            VARCHAR2(12)

Date and time interval data are an integral part of our day-to-day life. Common examples of interval data are the age of a person, the maturity period of a bond or certificate of deposit, and the warranty period of your car. Prior to Oracle9i Database, we all used the NUMBER data type to represent such data, and the logic needed to deal with interval data had to be coded at the application level. Oracle9i Database introduced two new data types to handle interval data:

The following sections discuss the use of these data types.

INTERVAL YEAR [ (precision for year) ] TO MONTH

                  CREATE TABLE event_history (
                  event_id NUMBER(10),
                  event_duration INTERVAL YEAR TO MONTH);

Table created.

DESC event_history 

 Name                         Null?    Type
 ---------------------------- -------- ------------------------
 EVENT_ID                              NUMBER(10)
 EVENT_DURATION                        INTERVAL YEAR(2) TO MONTH

                  INSERT INTO event_history VALUES (5001, NUMTOYMINTERVAL(2,'YEAR'));

1 row created.

INSERT INTO event_history VALUES (5002, NUMTOYMINTERVAL(2.5,'MONTH'));

1 row created.

SELECT * FROM event_history;

  EVENT_ID EVENT_DURATION
---------- ------------------
      5001 +02-00
      5002 +00-02

INTERVAL DAY [(precision for day)] 
TO SECOND [(precision for fractional seconds)]

                  CREATE TABLE batch_job_history (
                  job_id NUMBER(6),
                  job_duration INTERVAL DAY(3) TO SECOND(6));

Table created.

DESC batch_job_history

 Name                    Null?    Type
 ----------------------- -------- -----------------------------
 JOB_ID                           NUMBER(6)
 JOB_DURATION                     INTERVAL DAY(3) TO SECOND(6)

                  INSERT INTO batch_job_history VALUES
                  (6001, NUMTODSINTERVAL(5369.2589,'SECOND'));

1 row created.

SELECT * FROM batch_job_history;

    JOB_ID JOB_DURATION
---------- ----------------------------------------
      6001 +00 01:29:29.258900

DATE literals are specified in the format specified by SQL Standard, and take the following form:

DATE 'YYYY-MM-DD'

Unlike Oracle’s DATE data type, a DATE literal doesn’t specify any time information. You also can’t specify a format. If you want to specify a date literal, you 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.

                  YYYY-MM-DD

                  hh:mi:ss

A TIMESTAMP literal takes the following format:

TIMESTAMP 'YYYY-MM-DD 
               HH:MI:SS.xxxxxxxxx'

A TIMESTAMP literal can have up to nine digits of fractional seconds. The fractional part is optional, but the date and time elements are mandatory and must be provided in the given format. Here’s an example in which data is inserted into a table with a TIMESTAMP column:

               INSERT INTO transaction 
               VALUES (1001, TIMESTAMP '1998-12-31 08:23:46.368', 'OPEN'),

1 row created.

SELECT * FROM transaction;

TRANSACTION_ID TRANSACTION_TIMESTAMP             STATUS
-------------- --------------------------------- ---------
          1001 31-DEC-98 08.23.46.368000 AM      OPEN

A TIMESTAMP literal with a time zone displacement can be used to represent a literal of type TIMESTAMP WITH TIME ZONE. It takes the following form:

TIMESTAMP 'YYYY-MM-DD 
               HH:MI:SS.xxxxxxxxx {+|-} HH:MI'

Here is an example that shows how to insert data into a table with a TIMESTAMP WITH TIME ZONE column:

               INSERT INTO transaction_time_zone 
               VALUES (1002, TIMESTAMP '1998-12-31 08:23:46.368 -10:30', 'NEW'),

1 row created.

SELECT * FROM transaction_time_zone;

TRANSACTION_ID TRANSACTION_TIMESTAMP               STATUS
-------------- ----------------------------------- -------
          1002 31-DEC-98 08.23.46.368 AM -10:30    NEW

Even though the data type is called TIMESTAMP WITH TIME ZONE, the literal still uses just the TIMESTAMP keyword. However, the literal also specifies a date/time displacement for time zone using the {+|-} HH:MI notation.

If you are specifying a time zone displacement in a TIMESTAMP literal, you must specify the sign of the displacement (i.e., + or -). The range of the hour in a time zone displacement is -12 to +13, and the range of a minute is 0 to 59. A displacement outside these ranges will generate an error.

When you don’t specify a time zone displacement, the displacement is not assumed to be zero; instead, the timestamp is assumed to be in your session time zone, and the value of the displacement defaults to the displacement of that time zone. For example, the TIMESTAMP literal in the following INSERT specifies no time zone displacement, yet the SELECT statement proves that a time zone is, in fact, assumed:

               INSERT INTO transaction_time_zone 
               VALUES (1003, TIMESTAMP '1999-12-31 08:23:46.368', 'NEW'),

1 row created.

SELECT * FROM transaction_time_zone;

TRANSACTION_ID TRANSACTION_TIMESTAMP                 STATUS
-------------- ------------------------------------- -------
          1003 31-DEC-99 08.23.46.368 AM -05:00      NEW

There is no literal specifically for the TIMESTAMP WITH LOCAL TIME ZONE data type. To insert data into a column of this type, you use a TIMESTAMP literal with a time zone displacement. For example:

               INSERT INTO transaction_local_time_zone 
               VALUES (2001, TIMESTAMP '1998-12-31 10:00:00 -3:00', 'NEW'),

1 row created.

SELECT * FROM transaction_local_time_zone;

TRANSACTION_ID TRANSACTION_TIMESTAMP      STATUS
-------------- -------------------------- -------
          2001 31-DEC-98 08.00.00 AM      NEW

In a case like this, the time zone displacement is not stored in the database. The data is stored in the database in normalized form with respect to the database time zone. By “normalized form” we mean the input time is converted into a time in the database time zone before being stored in the database. The database time zone in this example is -5:00. Therefore, -3:00 is 2 hours ahead of the database time zone, and 10:00:00 - 3:00 is the same as 08:00:00 - 5:00. Since the time is normalized with respect to the database time zone, the displacement doesn’t need to be stored in the database.

Just as Oracle supports DATE and TIMESTAMP literals, it supports INTERVAL literals, too. There are two interval data types, and two types of corresponding interval literals: YEAR TO MONTH and DAY TO SECOND.

INTERVAL 'y [-m]' YEAR[(precision)] [TO MONTH]
INTERVAL 'm' MONTH[(precision)]

                  INSERT INTO event_history
                  VALUES (6001, INTERVAL '5-2' YEAR TO MONTH);

1 row created.

SELECT * FROM event_history;

  EVENT_ID EVENT_DURATION
---------- ------------------------------------------
      6001 +05-02

                  SELECT INTERVAL '4' YEAR FROM DUAL;

INTERVAL'4'YEAR
-------------------------------------------
+04-00

                  SELECT INTERVAL '3' MONTH FROM DUAL;

INTERVAL'3'MONTH
-------------------------------------------------
+00-03

SELECT INTERVAL '30' MONTH FROM DUAL;

INTERVAL'30'MONTH
-------------------------------------------------
+02-06

INTERVAL 'd [h[:m[:s]]]' unit1[(precision1)] TO unit2[(frac_precision)]

                  INSERT INTO batch_job_history
                  VALUES (2001, INTERVAL '0 3:16:23.45' DAY TO SECOND);

1 row created.

SELECT * FROM batch_job_history;

    JOB_ID JOB_DURATION
---------- ------------------------------------------------
      2001 +00 03:16:23.450000

                  SELECT INTERVAL '400' DAY(3) FROM DUAL;

INTERVAL'400'DAY(3)
-----------------------------------------------------------------
+400 00:00:00

SELECT INTERVAL '11:23' HOUR TO MINUTE FROM DUAL;

INTERVAL'11:23'HOURTOMINUTE
-----------------------------------------------------------------
+00 11:23:00

SELECT INTERVAL '11:23' MINUTE TO SECOND FROM DUAL;

INTERVAL'11:23'MINUTETOSECOND
-----------------------------------------------------------------
+00 00:11:23.000000

SELECT INTERVAL '20' MINUTE FROM DUAL;

INTERVAL'20'MINUTE
-----------------------------------------------------------------
+00 00:20:00

TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ are built-in SQL functions that convert, respectively, a character string into a DATE, a TIMESTAMP, and a TIMESTAMP WITH TIME ZONE. Input to these functions can be string literals, PL/SQL variables, and database columns of the CHAR and VARCHAR2 data types.

These three conversion functions are similar in operation. The difference is only in the data type of the return value. You call them as follows:

TO_DATE(string [,format])
TO_TIMESTAMP (string [,format])
TO_TIMESTAMP_TZ (string [,format])

The syntax elements are:

Through the format argument, Oracle provides a great deal of flexibility when it comes to converting between date and time values and text. Oracle provides far more flexibility, at least in our experience, than do competing platforms, such as DB2 and SQL Server.

Specifying a format is optional. When you don’t specify a format, the input string is assumed to be in a default format as specified by the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ parameter settings.

                  SELECT parameter, value
                  FROM nls_session_parameters
                  WHERE parameter LIKE '%FORMAT';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT                DD-MON-RR
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR

                  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

                  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.

                  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.

                  SELECT 
                       TO_TIMESTAMP_TZ('12/10/01 08:15:00.50 EST','MM/DD/YY HH:MI:SSXFF TZR')
                  FROM DUAL;

TO_TIMESTAMP_TZ('12/10/0108:15:00.50EST','MM/DD/YYHH:MI:SSXFFTZR')
---------------------------------------------------------------------------
10-DEC-01 08.15.00.500000000 AM EST

                  SELECT sent_to, report_id, 
                         TO_TIMESTAMP(report_id,'MMDDYYYY') date_generated
                  FROM report;

SENT_TO              REPORT_I DATE_GENERATED
-------------------- -------- ---------------------------------
Manager              01011999 01-JAN-99 12.00.00.000000000 AM
Director             01121999 12-JAN-99 12.00.00.000000000 AM
Vice President       01231999 23-JAN-99 12.00.00.000000000 AM

                  SELECT CAST('10-DEC-01' AS TIMESTAMP WITH LOCAL TIME ZONE) FROM DUAL;

CAST('10-DEC-01'ASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------------------------------------
10-DEC-01 12.00.00 AM

                  SELECT CAST(TO_TIMESTAMP_TZ('12/10/01','MM/DD/YY') 
                              AS TIMESTAMP WITH LOCAL TIME ZONE)
                  FROM DUAL;

CAST(TO_TIMESTAMP_TZ('12/10/01','MM/DD/YY')ASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------------------------------------
10-DEC-01 12.00.00 AM

The TO_YMINTERVAL and TO_DSINTERVAL functions are similar in purpose to the TO_DATE family of functions, and serve to convert character strings to the INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND data types. You can pass literals, PL/SQL variables, and database columns of CHAR or VARCHAR2 data type to these functions, which you invoke as follows:

TO_YMINTERVAL (string)
TO_DSINTERVAL (string)

In these invocations, string must contain character data convertible to an INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND value, and in one of the following formats:

The following two INSERT statements demonstrate the use of these functions:

INSERT INTO event_history VALUES (5001, TO_YMINTERVAL('02-04'));

INSERT INTO batch_job_history VALUES (6001, TO_DSINTERVAL('0 2:30:43'));

In this example, the string '02-04' represents an interval of 2 years and 4 months, while the string '0 2:30:43' represents an interval of 0 days, 2 hours, 30 minutes, and 43 seconds.

The NUMTOYMINTERVAL (NUMBER-TO-YEAR-MONTH-INTERVAL) and NUMTODSINTERVAL (NUMBER-TO-DAY-SECOND-INTERVAL) functions convert numeric values into INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND values, respectively. You invoke these functions as follows:

NUMTOYMINTERVAL (n, unit)
NUMTODSINTERVAL (n, unit)

The syntax elements are:

The following example demonstrates the use of these two functions. The first INSERT specifies an interval of two years, while the second specifies an interval of 5369.2589 seconds:

INSERT INTO event_history VALUES 
   (5001, NUMTOYMINTERVAL(2,'YEAR'));

INSERT INTO batch_job_history VALUES
   (6001, NUMTODSINTERVAL(5369.2589,'SECOND'));

Unlike the case with TO_YMINTERVAL and TO_DSINTERVAL, you cannot pass mixed units to these NUMTOXXINTERVAL functions. However, you can build up values from mixed units as follows:

INSERT INTO event_history VALUES 
   (7001, NUMTOYMINTERVAL(2,'YEAR') + NUMTOYMINTERVAL (4, 'MONTH'));

This INSERT creates a two-year and four-month interval by adding a two-year interval to a four-month interval.

The TO_CHAR function is the opposite of the TO_DATE and TO_TIMESTAMP functions, and converts a DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE value into a string of characters. Call TO_CHAR as follows:

TO_CHAR(temporal_data [,format])

The syntax elements are:

The format is optional. When the format is not specified, the format of the output depends upon the type of the input data:

The database parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT are discussed in Section 6.5.6.

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 timestamp into a string, and explicitly specifies a timestamp format:

               SELECT TO_CHAR(SYSTIMESTAMP, 'MM/DD/YYYY HH24:MI:SS.FF') FROM DUAL;

TO_CHAR(SYSTIMESTAMP,'MM/DD/Y
-----------------------------
12/12/2003 10:18:36.070000

The format element FF in the preceding example represents fractional seconds. Timestamp-specific formats are discussed in Section 6.5.

There are situations when you may need to combine TO_CHAR with TO_DATE. For example, if you want to know on what day of the week January 1, 2000, fell, you 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.

                  SELECT TO_CHAR(TO_DATE(TRUNC(3456.34),'J'),'Jsp') || ' Dollars and ' ||
                  TO_CHAR(TO_DATE(ROUND(MOD(3456.34,1)*100),'J'),'Jsp') || ' Cents' 
                  "Check Amount"
                  FROM DUAL;

Check Amount
-------------------------------------------------------------------
Three Thousand Four Hundred Fifty-Six Dollars and Thirty-Four Cents

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

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:

                  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.

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:

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:

The following sections describe ISO weeks and years with examples.

                  SELECT TO_CHAR(TO_DATE('10-DEC-01'),'WW'),
                         TO_CHAR(TO_DATE('10-DEC-01'),'IW') 
                  FROM DUAL;

TO TO
-- --
50 50

                  SELECT TO_CHAR(TO_DATE('10-DEC-99'),'WW'),
                         TO_CHAR(TO_DATE('10-DEC-99'),'IW')
                  FROM DUAL;

TO TO
-- --
50 49

                  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

                  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

                  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

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

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:

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

Oracle provides a number of helpful functions, some of which you’ve seen used earlier in this chapter, that you can use to manipulate temporal values:

SYSDATE is one of the most commonly used functions, and returns the current date and time as a DATE value:

               ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH:MI:SS';

Session altered.

SELECT SYSDATE FROM  DUAL;

SYSDATE
------------------
11-NOV-01 01:00:10

The following is an example of a function that takes arguments. The FROM_TZ function is used to add time zone information to the timestamp returned by a call to SYSTIMESTAMP. You can see that LOCALTIMESTAMP by itself returns no time zone information. FROM_TZ combines the TIMESTAMP with the time zone we specified, and returns a TIMESTAMP WITH TIME ZONE:

               SELECT LOCALTIMESTAMP FROM dual;

LOCALTIMESTAMP
--------------------------------------------------------
18-DEC-03 03.31.24.974000 PM

SELECT FROM_TZ(LOCALTIMESTAMP,'-5:00') FROM dual;

FROM_TZ(LOCALTIMESTAMP,'-5:00')
--------------------------------------------------------
18-DEC-03 03.31.25.024000 PM -05:00

The EXTRACT function is unusual in that its first argument is actually a SQL keyword, and the delimiter between arguments is also a keyword:

               SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2003

A more useful and interesting example of EXTRACT is shown at the end of Section 6.6.3.

Many of Oracle’s temporal functions take only DATE values as inputs. This harks back to the day when DATE was the only temporal type. You have to be careful about this, because Oracle will implicitly convert timestamp types to DATEs, leading you to inadvertently write erroneous code. For example:

               SELECT ADD_MONTHS(SYSTIMESTAMP,1) FROM dual;

ADD_MONTH
---------
18-JAN-04

The problem here isn’t terribly obvious, but what’s happened is that SYSTIMESTAMP has returned a TIMESTAMP WITH TIME ZONE value, which has been implicitly cast to a DATE, and thus both fractional seconds and the time zone have been lost. The results are the same as if you’d executed:

SELECT ADD_MONTHS(CAST(SYSTIMESTAMP AS DATE),1) FROM dual;

Be careful about passing TIMESTAMP values to functions that expect DATEs. If your code depends on fractional seconds or time zone information, you’ll lose that information, and your code won’t work as you expect.

Adding two datetime values doesn’t make sense. However, you can add days, months, years, hours, minutes, and seconds to a datetime to generate a future date and time. How you go about adding time intervals to datetime values depends on whether you are working with a DATE or one of the TIMESTAMP values.

                  SELECT SYSDATE, SYSDATE+1 FROM DUAL;

SYSDATE   SYSDATE+1
--------- ---------
05-OCT-01 06-OCT-01

                  SELECT SYSDATE+7 FROM DUAL;

SYSDATE+7
---------
12-OCT-01

                  SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH:MI:SS'),
                  TO_CHAR(SYSDATE+(20/1440),'DD-MON-YY HH:MI:SS')
                  FROM DUAL;

TO_CHAR(SYSDATE,'D TO_CHAR(SYSDATE+(2
------------------ ------------------
05-OCT-01 01:22:03 05-OCT-01 01:42:03

                  SELECT fname, hire_date, ADD_MONTHS(hire_date, 6) review_date 
                  FROM employee;

FNAME                HIRE_DATE REVIEW_DA
-------------------- --------- ---------
John                 22-OCT-99 22-APR-00

                  SELECT ADD_MONTHS('31-DEC-99',6) FROM DUAL;

ADD_MONTH
---------
30-JUN-00

                  select sysdate, sysdate + INTERVAL '0 6:27:00' DAY TO SECOND
                  from dual;

SYSDATE             SYSDATE+INTERVAL'06
------------------- -------------------
12/29/2003 15:39:00 12/29/2003 22:06:00

                  select sysdate, 
                         new_time(sysdate + INTERVAL '0 6:27:00' DAY TO SECOND, 'EST','PST')
                  from dual;

SYSDATE             NEW_TIME(SYSDATE+IN
------------------- -------------------
12/29/2003 15:39:00 12/29/2003 19:06:00

                  SELECT LOCALTIMESTAMP, LOCALTIMESTAMP + INTERVAL '0 3:16:23' DAY TO SECOND 
                  FROM DUAL;

LOCALTIMESTAMP
---------------------------------------------
LOCALTIMESTAMP+INTERVAL'03:16:23'DAYTOSECOND
---------------------------------------------
28-MAR-04 04.30.19.208000 PM
28-MAR-04 07.46.42.208000000 PM

                  SELECT LOCALTIMESTAMP + INTERVAL '1 0:00:00' DAY TO SECOND
                  FROM DUAL;

LOCALTIMESTAMP+INTERVAL'10:00:00'DAYTOSECOND
---------------------------------------------
29-MAR-04 04.36.46.211000000 PM

                  SELECT LOCALTIMESTAMP + NUMTODSINTERVAL(1,'DAY')
                  FROM DUAL;

LOCALTIMESTAMP+NUMTODSINTERVAL(1,'DAY')
-----------------------------------------
29-MAR-04 04.37.16.077000000 PM

Even though no other arithmetic operation (addition, multiplication, division) between two DATEs makes any sense, subtracting one DATE from another DATE is a very common and useful operation. The - operator allows you to subtract a DATE from a DATE, or a number from a DATE, a TIMESTAMP from a TIMESTAMP, an interval from a DATE and an interval from a TIMESTAMP.

                  SELECT order_nbr, expected_ship_dt - order_dt lead_time
                  FROM cust_order;

 ORDER_NBR  LEAD_TIME
---------- ----------
      1001          1
      1000          5
      1002         13
      1003         10
      1004          9
      1005          2
      1006          6
      1007          2
      1008          2
      1009          4
      1012          1
      1011          5
      1015         13
      1017         10
      1019          9
      1021          2
      1023          6
      1025          2
      1027          2
      1029          4

                  SELECT LOCALTIMESTAMP - transaction_timestamp FROM transaction;

SYSTIMESTAMP-TRANSACTION_TIMESTAMP
-----------------------------------
+000000453 07:04:39.086000

                  SELECT SYSDATE, SYSDATE - 1, SYSDATE - 7 FROM DUAL;

SYSDATE   SYSDATE-1 SYSDATE-7
--------- --------- ---------
05-OCT-01 04-OCT-01 28-SEP-01

                  SELECT SYSDATE, ADD_MONTHS(SYSDATE, -6) FROM DUAL;

SYSDATE   ADD_MONTH
--------- ---------
05-OCT-01 05-APR-01

                  SELECT SYSDATE-hire_date FROM employee;

SYSDATE-HIRE_DATE
-----------------
         714.0786

                  SELECT MONTHS_BETWEEN(SYSDATE,hire_date), 
                         MONTHS_BETWEEN(hire_date, SYSDATE)
                  FROM employee;

MONTHS_BETWEEN(SYSDATE,HIRE_DATE) MONTHS_BETWEEN(HIRE_DATE,SYSDATE)
--------------------------------- ---------------------------------
                        267.83499                        -267.83499

                  CREATE TABLE pizza_delivery (
                  order_id             NUMBER(10),
                  order_receive_time   DATE,
                  delivery_time        DATE);

Table created.

INSERT INTO pizza_delivery VALUES
                  (1, TO_DATE('12/20/03 08:47:53','MM/DD/YY HH:MI:SS'), 
                  TO_DATE ('12/20/03 10:30:34','MM/DD/YY HH:MI:SS'));

1 row created.

COMMIT;

Commit complete.

                  SELECT delivery_time - order_receive_time FROM pizza_delivery;

DELIVERY_TIME-ORDER_RECEIVE_TIME
--------------------------------
                       .07130787

                  SELECT EXTRACT(HOUR FROM 
                                 NUMTODSINTERVAL(delivery_time - order_receive_time, 'DAY'))
                         ||':'|| 
                         EXTRACT(MINUTE FROM 
                                 NUMTODSINTERVAL(delivery_time - order_receive_time, 'DAY')) 
                         ||':'||
                         EXTRACT(SECOND FROM 
                                 NUMTODSINTERVAL(delivery_time - order_receive_time, 'DAY')) 
                         "Lead Time"
                  FROM pizza_delivery;

Lead Time
---------------------------------------------------------------------------
1:42:41

                  SELECT TO_DATE('12/29/2003 08:00:00') - INTERVAL '0 0:30:00' DAY TO SECOND
                  FROM DUAL;

TO_DATE('12/29/2003
-------------------
12/29/2003 07:30:00

                  SELECT LOCALTIMESTAMP - INTERVAL '0 0:30:00' DAY TO SECOND 
                  FROM DUAL;

LOCALTIMESTAMP-INTERVAL'00:30:00'DAYTOSECOND
---------------------------------------------
28-MAR-04 03.42.59.819000000 PM

                  SELECT LOCALTIMESTAMP - INTERVAL '1 0:00:00' DAY TO SECOND
                  FROM DUAL;

LOCALTIMESTAMP-INTERVAL'10:00:00'DAYTOSECOND
----------------------------------------------
27-MAR-04 04.23.09.248000000 PM

                  SELECT LOCALTIMESTAMP - NUMTODSINTERVAL(1,'DAY')
                  FROM DUAL;

LOCALTIMESTAMP-NUMTODSINTERVAL(1,'DAY')
-----------------------------------------
27-MAR-04 04.27.41.052000000 PM

Oracle provides a built-in function to get the last day of a month. The function returns the last day of the month containing the input date. For example, to find the last date of the current month, you can use the following SQL statement:

               SELECT LAST_DAY(SYSDATE) "Next Payment Date" FROM DUAL;

Next Paym
---------
31-OCT-01

Sometimes it’s useful to be able to determine the first day of a given month; it would be nice if Oracle would provide a FIRST_DAY function. One approach to getting the first day of the month for a given date is to use the TRUNC function:

TRUNC(SYSDATE,'MM')

A side-effect of this approach is that any time-of-day component of the input value is eliminated; the result will always have a time of midnight at the beginning of the day. Such a truncation of time may be good, especially if you are doing a range comparison. For example, to find all employees hired in the current month, without applying any sort of function to the hire_date column:

SELECT * FROM employee
WHERE hire_date >= TRUNC(SYSDATE,'MM')
  AND hire_date < TRUNC(LAST_DAY(SYSDATE)+1);

This SELECT statement works because TRUNC(SYSDATE,'MM') sets the time-of-day to the very beginning of the first day of the month. The second TRUNC expression resolves to the very beginning of the first day of the following month, which is why less-than rather than less-than-or-equal-to is used when comparing hire_date to that value.

In many cases, TRUNC( date ,'MM') will work just fine for getting to the first day of a month. However, if you need to determine the first day of the month in which a given DATE value falls while also preserving the time-of-day, you can use the following expression:

ADD_MONTHS((LAST_DAY(SYSDATE)+1), -1)

This expression finds the last day of the month represented by date. It then adds 1 to get to the first day of the subsequent month, and finally uses ADD_MONTHS with an argument of -1 to go back to the beginning of the month in which you started. The result is the first day of the month in which the given date falls. Other approaches to this problem are possible; this is just one that works well for us. This approach has the advantage of preserving the time component of the date in question.

Rounding and truncating dates is similar in concept to the rounding and truncating of numbers, but more involved because an Oracle DATE contains date as well as time information. Use the ROUND function to round a date/time value to a specific element; use the TRUNC function to truncate a date/time value to a specific element.

The return value from ROUND or TRUNC depends upon the specified format, which is an optional parameter. If you don’t specify a format in the call to ROUND, the function returns a date by rounding the input to the nearest day. If you don’t specify a format in the call to TRUNC, that function returns the input date by setting the time component to the beginning of the day.

When using ROUND and TRUNC to round to the nearest day, or to truncate a date, the functions set the time fields of the return value to the beginning of the returned day—i.e., 12:00:00 AM (00:00:00 in HH24 format). For example:

               SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS AM'),
                      TO_CHAR(ROUND(SYSDATE), 'DD-MON-YY HH:MI:SS AM'),
                      TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YY HH:MI:SS AM')
               FROM DUAL;

TO_CHAR(SYSDATE,'DD-M TO_CHAR(ROUND(SYSDATE TO_CHAR(TRUNC(SYSDATE
--------------------- --------------------- ---------------------
06-OCT-01 07:35:48 AM 06-OCT-01 12:00:00 AM 06-OCT-01 12:00:00 AM

Notice that since the input time (SYSDATE) is before 12 noon, the output of ROUND and TRUNC are the same. However, if the input time were after 12 noon, the output of ROUND and TRUNC would be different, as in the following example:

               SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS AM'),
                      TO_CHAR(ROUND(SYSDATE), 'DD-MON-YY HH:MI:SS AM'),
                      TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YY HH:MI:SS AM')
               FROM DUAL;

TO_CHAR(SYSDATE,'DD-M TO_CHAR(ROUND(SYSDATE TO_CHAR(TRUNC(SYSDATE
--------------------- --------------------- ---------------------
06-OCT-01 05:35:48 PM 07-OCT-01 12:00:00 AM 06-OCT-01 12:00:00 AM

Since the input time is past 12 noon, ROUND returns the beginning of the next day. However, TRUNC still returns the beginning of the input date. This is similar to the rounding and truncating of numbers.

When you specify a format as an input to the ROUND and TRUNC functions, things become a bit more involved, but the concepts of rounding and truncating still remain the same. The difference is that the rounding and truncating are now based on the format you specify. For example, if you specify the format as YYYY, the input date will be truncated or rounded based on the year, which means that if the input date is before the middle of the year (July 1), both ROUND and TRUNC will return the first day of the year. If the input date is after July 1, ROUND will return the first day of the next year, whereas TRUNC will return the first day of the input year. For example:

               SELECT TO_CHAR(SYSDATE-180, 'DD-MON-YYYY HH24:MI:SS'),
                      TO_CHAR(ROUND(SYSDATE-180,'YYYY'),'DD-MON-YYYY HH24:MI:SS'),
                      TO_CHAR(TRUNC(SYSDATE-180,'YYYY'),'DD-MON-YYYY HH24:MI:SS')
               FROM DUAL;

TO_CHAR(SYSDATE-180, TO_CHAR(ROUND(SYSDAT TO_CHAR(TRUNC(SYSDAT
-------------------- -------------------- --------------------
09-APR-2001 20:58:33 01-JAN-2001 00:00:00 01-JAN-2001 00:00:00

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
                      TO_CHAR(ROUND(SYSDATE,'YYYY'),'DD-MON-YYYY HH24:MI:SS'),
                      TO_CHAR(TRUNC(SYSDATE,'YYYY'),'DD-MON-YYYY HH24:MI:SS')
               FROM DUAL;

TO_CHAR(SYSDATE,'DD- TO_CHAR(ROUND(SYSDAT TO_CHAR(TRUNC(SYSDAT
-------------------- -------------------- --------------------
06-OCT-2001 20:58:49 01-JAN-2002 00:00:00 01-JAN-2001 00:00:00

Similarly, you can round or truncate a date to a specific month, quarter, week, century, hour, minute, and so forth by using the appropriate format. Table 6-2 lists the formats (and their meanings) that can be used with the ROUND and TRUNC functions.

There are times when you need to SELECT data from a table based on a given date range. Let’s say you have been asked to print all disputed orders placed on a given date, say 22-JUL-01. Most likely, your immediate response would be a query such as the following:

               SELECT * FROM disputed_orders
               WHERE order_dt = '22-JUL-01';

no rows selected

There’s no output. Surprised? Although you know there are orders on 22-JUL-01, this query didn’t return any rows. The reason is that order_dt is a DATE column, and contains time as well as date information. On the other hand, the date literal '22-JUL-01' doesn’t contain any time information. When you don’t specify the time portion in a date literal, the time portion is assumed to be beginning of the day—i.e., 12:00:00 A.M. (or 00:00:00 in 24 hour format). In the disputed_orders table, the time components in the order_dt column are other than 12:00:00 A.M. In this case, the correct query to print orders placed on 22-JUL-01 is:

               SELECT order_nbr, cust_nbr, order_dt, expected_ship_dt
               FROM disputed_orders
               WHERE order_dt BETWEEN 
                              TO_DATE('22-JUL-01 00:00:00','DD-MON-YY HH24:MI:SS') AND
                              TO_DATE('22-JUL-01 23:59:59','DD-MON-YY HH24:MI:SS'),

ORDER_NBR   CUST_NBR ORDER_DT  EXPECTED_
--------- ---------- --------- ---------
     1001          1 22-JUL-01 23-JUL-01
     1005          8 22-JUL-01 24-JUL-01
     1006          1 22-JUL-01 28-JUL-01
     1012          1 22-JUL-01 23-JUL-01
     1021          8 22-JUL-01 24-JUL-01
     1023          1 22-JUL-01 28-JUL-01

The query treats the one day as a range: 22-JUL-01 00:00:00 through 22-JUL-01 23:59:59. Thus, the query returns any order placed at any time during 22-JUL-01.

Another way to solve this problem of needing to ignore the time components in a DATE column is to truncate the date, and then compare the truncated result with the input literal:

               SELECT order_nbr, cust_nbr, order_dt, expected_ship_dt
               FROM disputed_orders
               WHERE TRUNC(order_dt) = '22-JUL-01';

ORDER_NBR   CUST_NBR ORDER_DT  EXPECTED_
--------- ---------- --------- ---------
     1001          1 22-JUL-01 23-JUL-01
     1005          8 22-JUL-01 24-JUL-01
     1006          1 22-JUL-01 28-JUL-01
     1012          1 22-JUL-01 23-JUL-01
     1021          8 22-JUL-01 24-JUL-01
     1023          1 22-JUL-01 28-JUL-01

The TRUNC function sets the time portion to the beginning of the day. Therefore, the equality comparison with the date literal '22-JUL-01' returns the expected output. The same result can be achieved by converting order_dt to a character string in a format matching that of the input data:

SELECT * FROM disputed_orders
WHERE TO_CHAR(order_dt,'DD-MON-YY') = '22-JUL-01';

The downside to the approach of using the TRUNC and TO_CHAR functions is that the resulting query cannot make use of any index that happens to be on the order_dt column. This can have significant performance implications. On the other hand, the date range solution, while more complex to code, does not preclude the use of any index on the column in question.

You can use the same techniques shown in this section to SELECT data based on any given date range, even if that range spans more than just one day.

For certain types of queries, it’s helpful to have a table with one row for each date over a period of time. For example, you might wish to have one row for each date in the current year. You can use the TRUNC function in conjunction with some PL/SQL code to create such a table:

               CREATE TABLE dates_of_year (one_day DATE);

Table created.

DECLARE
                 i NUMBER;
                 start_day DATE := TRUNC(SYSDATE,'YY'),
               BEGIN
                 FOR i IN 0 .. (TRUNC(ADD_MONTHS(SYSDATE,12),'YY') - 1) - (TRUNC(SYSDATE,'YY')) 
                 LOOP
                   INSERT INTO dates_of_year VALUES (start_day+i);
                 END LOOP;
               END;
               /

PL/SQL procedure successfully completed.

SELECT COUNT(*) FROM dates_of_year;

  COUNT(*)
----------
       365

The dates_of_year table is now populated with the 365 days of the year 2001. You can now use this table to generate various useful lists of dates.

Let’s say there are two paydays where you work—the 15th of each month and the last day of each month. Use the following query against the dates_of_year table to generate a list of all paydays in the year 2001:

               SELECT one_day payday FROM dates_of_year
               WHERE TO_CHAR(one_day,'DD') = '15'
               OR one_day = LAST_DAY(one_day);

PAYDAY
---------
15-JAN-01
31-JAN-01
15-FEB-01
28-FEB-01
15-MAR-01
31-MAR-01
15-APR-01
30-APR-01
15-MAY-01
31-MAY-01
15-JUN-01
30-JUN-01
15-JUL-01
31-JUL-01
15-AUG-01
31-AUG-01
15-SEP-01
30-SEP-01
15-OCT-01
31-OCT-01
15-NOV-01
30-NOV-01
15-DEC-01
31-DEC-01

24 rows selected.

Quite often you are told by a government organization that the processing of a document will take “x” number of days. When someone says something like that, they usually mean “x” number of working days. Therefore, to calculate the expected completion date, you need to count “x” days from the current date, skipping Saturdays and Sundays. Obviously, you can’t use simple date arithmetic, because simple date subtraction doesn’t exclude weekend days. What you can do is use the dates_of_year table. For example:

               SELECT COUNT(*) FROM dates_of_year
               WHERE RTRIM(TO_CHAR(one_day,'DAY')) NOT IN ('SATURDAY', 'SUNDAY')
               AND one_day BETWEEN '&d1' AND '&d2';

Enter value for d1: 18-FEB-01
Enter value for d2: 15-MAR-01
old   3: AND one_day BETWEEN '&d1' AND '&d2'
new   3: AND one_day BETWEEN '18-FEB-01' AND '15-MAR-01'

  COUNT(*)
----------
        19

This query counts the number of days between the two dates you enter, excluding Saturdays and the Sundays. The TO_CHAR function with the `DAY’ format converts each candidate date (from the dates_of_year table) to a day of the week, and the NOT IN operator excludes the days that are Saturdays and Sundays. Notice the use of the RTRIM function with TO_CHAR. We used RTRIM because TO_CHAR produces the DAY as a nine-character string, blank padded to the right. RTRIM eliminates those extra spaces.

There could be holidays between two dates, and the queries shown in this section don’t deal with that possibility. To take holidays into account, you need another table (perhaps named holidays) that lists all the holidays in the year. You can then modify the previous query to exclude days listed in the holidays table. Try this as an exercise.

Let’s say you want to print a quarterly summary of all your orders. You want to print the total number of orders and total sale price for each quarter. The order table is as follows:

               SELECT * FROM cust_order;

ORDER CUST  SALES  PRICE ORDER_DT  EXPECTED_ CANCELLED SHIP STATUS
----- ---- ------ ------ --------- --------- --------- ---- -----------
 1001    1   7354     99 22-JUL-01 23-JUL-01                DELIVERED
 1000    1   7354        19-JUL-01 24-JUL-01 21-JUL-01      CANCELLED
 1002    5   7368        12-JUL-01 25-JUL-01 14-JUL-01      CANCELLED
 1003    4   7654     56 16-JUL-01 26-JUL-01                DELIVERED
 1004    4   7654     34 18-JUL-01 27-JUL-01                PENDING
 1005    8   7654     99 22-JUL-01 24-JUL-01                DELIVERED
 1006    1   7354        22-JUL-01 28-JUL-01 24-JUL-01      CANCELLED
 1007    5   7368     25 20-JUL-01 22-JUL-01                PENDING
 1008    5   7368     25 21-JUL-01 23-JUL-01                PENDING
 1009    1   7354     56 18-JUL-01 22-JUL-01                DELIVERED
 1012    1   7354     99 22-JUL-01 23-JUL-01                DELIVERED
 1011    1   7354        19-JUL-01 24-JUL-01 21-JUL-01      CANCELLED
 1015    5   7368        12-JUL-01 25-JUL-01 14-JUL-01      CANCELLED
 1017    4   7654     56 16-JUL-01 26-JUL-01                DELIVERED
 1019    4   7654     34 18-JUL-01 27-JUL-01                PENDING
 1021    8   7654     99 22-JUL-01 24-JUL-01                DELIVERED
 1023    1   7354        22-JUL-01 28-JUL-01 24-JUL-01      CANCELLED
 1025    5   7368     25 20-JUL-01 22-JUL-01                PENDING
 1027    5   7368     25 21-JUL-01 23-JUL-01                PENDING
 1029    1   7354     56 18-JUL-01 22-JUL-01                DELIVERED

20 rows selected.

There is no quarter column in the cust_order table. You have to manipulate the order_dt column to generate the quarter. The following SQL statement does this using the TO_CHAR function along with a date format. In addition to being used in the SELECT list, notice that TO_CHAR is used in the GROUP BY clause to group the results by quarter:

               SELECT 'Q'||TO_CHAR(order_dt, 'Q') quarter, COUNT(*), 
                      SUM(NVL(sale_price,0))        
               FROM cust_order
               GROUP BY 'Q'||TO_CHAR(order_dt, 'Q'),

QU   COUNT(*) SUM(NVL(SALE_PRICE,0))
-- ---------- ----------------------
Q3         20                    788

Using this same technique, you can summarize data by week, month, year, hour, minute, or any other date/time unit that you choose.