T10        6 01-MAR-97 10:00 AM
T13        2 01-MAR-97 01:00 PM
T13        4 01-MAR-97 01:00 PM
T13        6 01-MAR-97 01:00 PM
T15        2 01-MAR-97 03:00 PM
T15        4 01-MAR-97 03:00 PM
T15        6 01-MAR-97 03:00 PM
TT9        3 01-MAR-97 09:00 AM
TT9        5 01-MAR-97 09:00 AM

12 rows selected.

Table 7.1 contains a list of the valid elements that can be used in the date format.

Table 7.1. List of date format elements.

                                                 continues

Table 7.1. continued

The Oracle Default Date Format

The default date format is DD-MON-YY. For instance, 01-JAN-98 is a date in accordance with Oracle's default date format. You can specify dates with this format model without using any other functions or datatype conversion. But if you need to display or specify dates in a different format, then you'll need to use a built-in function to specify the format model you want to use.

If you try to assign a string that doesn't adhere to this default format to a date column, Oracle will probably return an error. For example, if the first two digits are greater than 31, Oracle will always return the error code ORA-01847. If the abbreviation for the month is not JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, or DEC, Oracle will return error code ORA-01843. If the day of the month is not within the valid range for that particular month, Oracle will return error code ORA-01839. Table 7.2 contains a list of Oracle error codes related to the manipulation of date values.

Table 7.2. Oracle error codes related to dates.

Converting Dates to Strings

You need to remember that a date column value remains a date until you convert it to some other datatype. If, for example, you want to extract the first character of a date column value, you'll need to convert the value to a string using a built-in function named TO_CHAR.

The format for this function is

TO_CHAR(date_value,date_format)

The variables are defined as follows:

date_value is a literal date value, a date value from a column, or a date value returned by a built-in function. date_format is a valid Oracle date format.

Listing 7.19 shows how a query can use the TO_CHAR function to return the employee hire date using the format MONTH DD, YYYY.

Listing 7.19. Converting a date value to a string.

SQL> select Last_Name, First_Name,
     2  to_char(Hire_Date,'MONTH DD, YYYY') H_DATE
      3  from Employee
       4  order by Hire_Date;
LAST_NAME                      FIRST_NAME           H_DATE
------------------------------ -------------------- --------------------
SMITH                          JEAN                 APRIL     10, 1982
HERNANDEZ                      RANDY                NOVEMBER  18, 1983
GLEASON                        PAUL                 APRIL     05, 1984
BARRETT                        SARAH                JANUARY   16, 1989
HIGGINS                        BEN                  FEBRUARY  11, 1989
YEN                            CINDY                JUNE      09, 1991
GILROY                         MAX                  SEPTEMBER 22, 1992
CARSON                         BETH                 DECEMBER  12, 1992
SWANSON                        HARRY                MAY       18, 1993

9 rows selected.

Once a date value has been converted to a string with the TO_CHAR function, you can use it as an argument in other string functions. For example, you can use the function SUBSTR to extract a substring from a string. Listing 7.20 demonstrates how to use the SUBSTR function to extract the first letter of the employee's month of hire.

Listing 7.20. Embedding the TO_CHAR function within the SUBSTR function.

SQL> select Last_Name, First_Name,
         2  substr(to_char(Hire_Date,'MON'),1,1) the_first_letter_of_the_month
     3  from Employee
       4  order by the_first_letter_of_the_month;
LAST_NAME                      FIRST_NAME           T
------------------------------ -------------------- -
SMITH                          JEAN                 A
GLEASON                        PAUL                 A
CARSON                         BETH                 D
HIGGINS                        BEN                  F
BARRETT                        SARAH                J
YEN                            CINDY                J
SWANSON                        HARRY                M
HERNANDEZ                      RANDY                N
GILROY                         MAX                  S

9 rows selected.

The next section looks at some of the many ways in which dates and times can be displayed. You have a tremendous amount of flexibility in how to display and specify these values.

Converting Strings to Dates

Not surprisingly, the conversion of string values to dates is similar to the conversion of dates to strings. Instead of using the TO_CHAR built-in function, you use the TO_DATE built-in function because the goal is to specify a date value using a legal date format.

The arguments of the TO_DATE function are the reverse of the arguments of the TO_CHAR function.

TO_DATE (string_value, date_format)

The variables are defined as follows:

string_value is a literal string value, a string value from a column, or a string value returned by a built-in function.
date_format is a valid Oracle date format.

For example, if you want to convert a string that doesn't use the Oracle default date format (DD-MON-YY), you would use the TO_DATE function. Listing 7.21 shows how a query can be used to determine the number of days that have elapsed since the American bicentennial.