Listing 7.15. Using the DECODE function to translate a numeric value into a string value.

SQL> select Schedule_ID, Day,
  2  decode (Day, 1, `SUN', 2, `MON', 3, `TUE', 4, `WED',
  3               5, `THU', 6, `FRI', 7, `SAT')
   4  from Schedule_Type_Details
  5  order by Schedule_ID, Day;

SCHEDULE_ID                DAY DEC
-------------------- --------- ---
S180                         6 FRI
T10                          2 MON
T10                          4 WED
T10                          6 FRI
T13                          2 MON
T13                          4 WED
T13                          6 FRI
T15                          2 MON
T15                          4 WED
T15                          6 FRI
TT9                          3 TUE
TT9                          5 THU

12 rows selected.

Converting a Character to Its ASCII Numeric Value

At some point you may want to obtain the ASCII numeric equivalent of a character in a column. The ASCII function serves this purpose. It has a single string argument. ASCII returns the ASCII numeric equivalent of the first character of its argument. Listing 7.16 contains an example of the use of the ASCII function.

Listing 7.16. Using the ASCII function.

SQL> select Last_Name, ASCII(Last_Name)
    2  from Instructor
   3  order by Last_Name;

LAST_NAME                 ASCII(LAST_NAME)
------------------------  ----------------
ANGELO                                  65
BATES                                   66
BILLINGS                                66
CHANG                                   67
CHERNOW                                 67
CHU                                     67
DANIELS                                 68
EDWARDS                                 69
HITCHCOCK                               72
JASON                                   74

                                                               continues

Listing 7.16. continued

NILAND                                  78
PARKER                                  80
POULSON                                 80
RESTON                                  82
RICHARDSON                              82
TORRES                                  84
WEISS                                   87
YOUNG                                   89

18 rows selected.

Manipulating Dates

You will find tremendous variation in the way that database systems treat dates and times. Fortunately, Oracle provides a special datatype—the date—for dealing with dates and times. This datatype has its own internal format with which you needn't be concerned, other than knowing that it provides for the storage of the century, year, month, day, hour, minute, and second. As this lesson points out, using this datatype where appropriate has many advantages.

The Oracle date Datatype

The Oracle date datatype is efficient because it requires only seven bytes of storage. In addition, when you define a column as a date, you can use all of Oracle's built-in functions that manipulate dates and times.

The Oracle date datatype is also extremely convenient for the application developer. One can argue about whether or not the algorithms used by Oracle are optimal; you may feel that other methods are more efficient for storing date and time values. But without question, using the Oracle date datatype can save you a significant amount of time and effort in application development. In fact, because the advantages of this datatype are so clear, you really should use the Oracle date datatype whenever you need to store date or time information.

Using the date Datatype to Store Time Information

The Oracle date datatype also stores time information: hour, minute, and second. You can use a column defined as a date to store only date information, only time information, or both.

If you choose not to use the date datatype for storing date and time information, you will be forced to use other algorithms for manipulating the formats you have defined. You won't be able to use any of Oracle's built-in functions for manipulating dates and times. A task that could have been accomplished in a single SELECT statement will require additional processing in a programming language or development environment. The following scenario shows the consequences of not using the Oracle date datatype when its use is appropriate.

Suppose that you need to store the hire date for each instructor and a possible termination date. If you made an incorrect decision and decided to store the instructor's hire date as a numeric value in the format YYMMDD, the hire date for an employee hired on May 9, 1957, would be stored as 570509. Listing 7.17 contains a description of the Instructor table if it included a column for storing the hire date.

Listing 7.17. Instructor table with hire date stored as a number.

SQL> desc Instructor
 Name                            Null?    Type
 ------------------------------  -------- ----
 INSTRUCTOR_ID                            VARCHAR2(20)
 DEPARTMENT_ID                   NOT NULL VARCHAR2(20)
 LAST_NAME                       NOT NULL VARCHAR2(25)
 FIRST_NAME                               VARCHAR2(25)
 MI                                       VARCHAR2(1)
 POSITION                                 VARCHAR2(25)
 TELEPHONE                                VARCHAR2(10)
 FAX                                      VARCHAR2(10)
 EMAIL                                    VARCHAR2(100)


 HIRE_DATE                                NUMBER(6)

Cleverly (or so you thought), you decided to use a format of YYMMDD so that the hire date could be ordered either in ascending or descending order. You could use a SELECT statement to retrieve rows from the Instructor table ordered by hire date. But this approach has a few problems, namely:

• An erroneous assumption. When an instructor is hired after 1999, the preceding SELECT statement will not return the correct result. An instructor hired in 2000 and beyond will show up at the beginning of the list because the format stores only the last two digits of the year in which an instructor was hired.
• More work and lower reliability. Because you can't rely on Oracle date validation, the burden of developing an algorithm for validating a hire date is on your shoulders.
• Limited functionality. If you need to query a table based on date arithmetic, what could have been accomplished with a single SELECT statement incorporating built-in functions will instead require additional processing. You can't write a SELECT statement that will retrieve all employees whose hire date is less than the mean hire date.

Using the Oracle date datatype instead helps to ensure that any application you develop will be portable to other platforms.

Date formats are supported on every platform on which Oracle runs. If you are planning to run an application on a variety of operating systems, you'll find that using the Oracle date datatype is easier than trying to support a variety of date and time formats for each operating system.

The Current Date and Time: SYSDATE

Oracle has a number of values called pseudocolumns that can be referenced in SQL statements. One of these values is SYSDATE. Despite its name, SYSDATE also contains time information. Like the date datatype, SYSDATE is accurate to the nearest second. SYSDATE is an extremely useful construct for time-stamping rows during an insert or update operation. Many of the examples in this lesson use SYSDATE.

The Oracle Date Format Model

Because the date datatype stores the values for century, year, day, month, hour, minute, and second, each of these values can be extracted and formatted independently. Also, the date and time elements can be abbreviated or fully spelled out according to the format that you specify.

You also have the option of repeating a date or time element in different formats. As you recall, the Schedule_Type_Details table contains the starting time for each day of a particular Schedule ID. For example, one schedule might describe a Monday/Wednesday/Friday schedule that begins at 10:00 a.m. and has a duration of 50 minutes. The Starting Time column contains only information about the starting hour and minute. Therefore, you must use the to_char function, along with a date/time format, to translate the internal date value to an external character value. Listing 7.18 shows how you can display the contents of the Schedule_Type_Details table.

Listing 7.18. Converting a date and time value to a string.

SQL> select Schedule_ID, Day, Starting_Time, to_char(Starting_Time,'HH:MI PM')
     2  from Schedule_Type_Details
    3  order by Schedule_ID, Day;

SCHED    DAY STARTING_ TO_CHAR(STARTING_TIME,'HH:MIPM')
----- ------ --------- ------------------------------------------------
S180       6 01-MAR-97 09:00 AM
T10        2 01-MAR-97 10:00 AM
T10        4 01-MAR-97 10:00 AM