Table 7.7. Date format elements.

                                                          continues

Table 7.7. continued

Finally, the last part of the TO_DATE function is NLS_DATE_LANGUAGE. For all you network gurus, this is simply the language you want returned, such as English, Spanish, and so on. Remember, certain functions will only return values in the English language, such as SPTH, SP, and so forth.

Just to confuse you even further, take a look at the syntax of the TO_DATE function using Julian days. Again, this is the number of days that have elapsed since January 1, 4712 BC.

TO_DATE(number, format, NLS_Params)

The syntax is not much different than the previous syntax for normal character-based dates, except that you are now passing to the function a number value that represents the Julian days.

As you can see, this simple function can be formatted in many different ways. The best way is to go ahead and type in all the following listings to see your output. These examples use SQL*Plus as a quick method for testing, but these could easily be used in your PL/SQL code except where specified as SQL only. Go ahead and enter and execute Listings 7.1 and 7.2.

INPUT
Listing 7.1. Converting number representation to DATE format.

SELECT TO_DATE(`061167','MMDDYY') "Birthday" from DUAL;

OUTPUT
The output will appear as

Birthday
--------
11-JUN-67

INPUT
Listing 7.2. Converting spelled date to DATE format.

SELECT TO_DATE(`January 15','MONTH DD') "Sample" from DUAL;

OUTPUT
Your output should appear similar to the following:

Sample
--------
15-JAN-97

ANALYSIS Notice that even though the example did not specify the century or the year, it takes the default system century and year.

What are some of the possible errors that you can encounter with TO_DATE? What if you leave off the mask, or incorrectly specify the mask? Listing 7.3 reflects a sample error.

INPUT
Listing 7.3. Errors with TO_DATE.

SELECT TO_DATE(`061167') "Error" from DUAL;

OUTPUT
You should get the error message

ERROR:
ORA-01843: not a valid month


no rows selected

Because you did not apply a mask, the standard date mask was applied from the format DD-MON-YY. Because 06 is a valid day, Oracle had no problems with handling of the day. However, when the program arrives at the month, the default date used the three-letter abbreviation for the month. The value 116 is not a valid abbreviation for a month. Listing 7.4 shows an instance when the default date can be used.

INPUT
Listing 7.4. Proper use of default format mask.

SELECT TO_DATE('06-Jan-67') "Correct" from DUAL;

OUTPUT
The output will appear as

Correct
--------
06-JAN-67

As you can see, making sure that you pass the format mask in the same manner as the character string is highly important!

The next example demonstrates using TO_DATE as part of PL/SQL code for practice. Enter the code in Listing 7.5.

INPUT
Listing 7.5. Using TO_DATE within PL/SQL.

1: DECLARE
2:      v_Convert_Date DATE;
3: BEGIN
4:      v_Convert_Date := TO_DATE(`061167','MMDDYY');
5:      DBMS_OUTPUT.PUT_LINE(`The converted date is: ` || v_Convert_Date);
6: END;
7: /