Listing 7.21. An example of date arithmetic.

SQL> select SYSDATE - TO_DATE('07-04-1976','MM-DD-YYYY')
      2  from dual;
SYSDATE-TO_DATE('07-04-1976','MM-DD-YYYY')
------------------------------------------
                                 6878.9465

Dates and Time

Remember that every column defined using the date datatype contains both a date and a time value. If you are interested in storing only a time value in this column, the date value will be set to a default value. Listing 7.21 illustrates the use of a common format for displaying time—HH:MI:SS.

Listing 7.21. Using a time format in the TO_CHAR function.

SQL> select Employee_ID,
Âto_char(Time_Clocked_In,'HH:MI:SS') Time_Clocked_In
        2  from Time_Clock
        3  order by Employee_ID;
EMPLOYEE_ID TIME_CLOCKED_IN
----------- ----------------------------------------------------------
       1002 09:02:03
       1003 08:51:12
       1004 08:59:33
       1005 09:22:12

Remember that the Oracle date datatype is capable of storing time to the nearest second.

If you want to use a 24-hour time format, the time format should be specified in the following way:

SELECT TO_CHAR(arrival_time,'HH24:MI:SS') FROM DUAL;

Oracle also has a time format model that enables you to express a time as seconds past midnight. In the following example, assume that the current time is 2:00 a.m.:

SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;
7200      which is the equivalent of two hours

By using the time format model SSSSS, Oracle returns the date expressed in seconds past midnight. The time 2:00 a.m. represents two hours past midnight, which is equal to 7,200 seconds.

Calculating the Difference Between Two Dates

Another advantage of using the Oracle date datatype is that it supports date arithmetic. You can add or subtract days from an existing date, for example:

select sysdate + 7 from dual;

By adding 7 to SYSDATE, you can obtain the date a week from the current date. Similarly, you can subtract days from a date value to calculate an earlier date.

Specifying a Numeric Column

As you've already seen, a column's datatype is specified in the CREATE TABLE and ALTER TABLE statements.

The general syntax for specifying a numeric datatype is

NUMBER  ([precision  [, scale]])

The variables are defined as follows:

precision is an optional argument that specifies the number of digits of precision that Oracle should store for column values. scale is an optional argument indicating the number of digits to the right of the decimal point that Oracle should store for column values.

If you don't specify precision or scale, Oracle accepts a number of up to 38 digits of precision—the maximum precision that Oracle offers. When you specify a column, consider limiting the width of numeric values by using an appropriate precision. For example, if a

column stores a patient's body temperature in degrees Fahrenheit, you would specify the column as

Body_Temp_F Number(4,1)

A precision of 4 and a scale of 1 allow Body_Temp_F to store a total of four digits, including one digit to the right of the decimal point. Listing 7.22 demonstrates how temperatures that are within the specified precision and scale are accepted by the Oracle database.

Listing 7.22. Oracle accepts numbers within specified precision and scale.

SQL> update Patient
      2  set Body_Temp_F = 99.2
      3  where
       4  Patient_ID = `A2002';
    1 row updated.
SQL> update Patient
       2  set Body_Temp_F = 103.8
       3  where
        4  Patient_ID = `E3893';
     1 row updated.

Listing 7.23 illustrates how the Oracle database doesn't allow a value that violates the precision and scale to be stored.

Listing 7.23. Oracle database rejects values outside specified precision and scale.

SQL> update Patient
      2  set Body_Temp_F = 1003.8
      3  where
      4  Patient_ID = `N3393';
      set Body_Temp_F = 1003.8
                *
    ERROR at line 2:
   ORA-01438: value larger than specified precision allows for this column

Of course, this definition for Body_Temp_F allows values up to 999.9 degrees Fahrenheit—an impossible value for humans. In addition to specifying the precision and scale, you also need to specify a CHECK constraint for this column to restrict its values to a range.

If you specify a value for precision but not for scale, Oracle truncates the fractional value of a real number before storing the value in the column. This concept is shown in Listing 7.24.

Listing 7.24. Fractional value is truncated.

SQL> create table Number_Demo (
  2  Int_Value    number(3),
   3  Real_Value   number(3,1),
  4  Num_Value    number);
Table created.

SQL> insert into Number_Demo
   2  (Int_Value)
  3  values
   4  (12.2);
1 row created.

SQL> select Int_Value
  2  from Number_Demo;
INT_VALUE
---------
       12

If values for precision and scale have been furnished and you store a numeric value whose scale exceeds the column's scale, Oracle truncates the fractional value to the column's scale, as shown in Listing 7.25.

Listing 7.25. Fractional value is truncated to scale defined for column.

SQL> insert into Number_Demo
      2  (Real_Value)
      3  values
      4  (3.144);
1 row created.

SQL> select Real_Value
     2  from Number_Demo;
REAL_VALUE
----------
       3.1

In addition to the NUMBER datatype, Oracle accepts the following keywords that describe a numeric column:

• NUMERIC, DECIMAL, and DEC
• INTEGER, INT, and SMALLINT
• FLOAT, DOUBLE PRECISION, and REAL

Oracle supports these other datatypes to provide compatibility with ANSI SQL and other relational database systems such as IBM SQL/DS and DB2. The NUMERIC, DECIMAL, and DEC