OUTPUT
All the PL/SQL code does is to create a variable of type DATE in line 2 and assign it to the converted character date in line 4. The output should be

The converted date is: 11-JUN-67

PL/SQL procedure successfully completed.

How many people know a foreign language? How do you convert to the Oracle built-in DATE from another language? Remember to use the NLS_DATE_LANGUAGE parameter to specify the language. Listing 7.6 is an example of converting a German date to an Oracle DATE.

INPUT
Listing 7.6. Converting German date to DATE format.

SELECT TO_DATE(`februar-23','MONTH-DD','NLS_DATE_LANGUAGE=german')
     "Converted" from DUAL;

OUTPUT
The output will appear in the default Oracle format as

Converted
--------
23-FEB-97

Finally, how about some calculations on that date just returned? Listing 7.7 reflects adding 10 days to the converted date.

INPUT
Listing 7.7. Performing calculations on a converted date.

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

OUTPUT
Your output should appear as follows:

The converted date is: 21-JUN-67

PL/SQL procedure successfully completed.

You have taken a converted date of 06/11/67 and added 10 days in line 4, which brings you to the proper result of 21-JUN-67!

You should now be starting to get a grasp on how Oracle converts characters to dates. Experiment with some of the other formats in the rest of this section.

Using TO_CHAR for Dates

If you can turn character strings into dates, you should be able to reverse this process. Oracle provides you with the answer by using the TO_CHAR function.

The Syntax for the TO_CHAR Function

TO_CHAR(date, format, NLS_Params)

Remember to refer to Table 7.7 for allowable mask formats. The best way to demonstrate TO_CHAR is through many examples, especially with format varieties. The first example will take the current system date and time from the SYSDATE function and format it to a spelled-out date in Listing 7.8.

INPUT
Listing 7.8. Converting DATE to spelled-out character format.

SELECT TO_CHAR(SYSDATE,'MONTH DDTH YYYY') "Today" from DUAL;

OUTPUT
Your output should appear as follows:

Today
--------------------
JUNE      03RD 1997

How about using this as a PL/SQL procedure and going back to the good old days of BC? Listing 7.9 shows another example of using TO_CHAR with converting and formatting dates.

INPUT
Listing 7.9. Converting DATE to spelled-out character format.

 1: DECLARE
 2:      v_Convert_Date DATE := TO_DATE(`06112067BC','MMDDYYYYBC');
 3:      v_Hold_Date VARCHAR2(100);
 4: BEGIN
 5:      v_Hold_Date := TO_CHAR(v_Convert_Date,'MMDDSYYYY');
 6:      DBMS_OUTPUT.PUT_LINE(`The converted date is: ` || v_Hold_Date);
 7: END;
 8: /

ANALYSIS There are several items to make note of here. First, in order to assign a date to a DATE value, you need to convert a character date to a DATE datatype by using the TO_DATE function, as shown in line 2. If you simply tried to enter or assign a date such as in the following example, you would generate an error:

v_Convert_Date DATE := 11-JUN-67;

The second item to notice is how Oracle displays dates for BC. When you run the code from Listing 7.9, your output will appear as follows:

OUTPUT

The converted date is: 0611-2067

PL/SQL procedure successfully completed.

As you can see, a negative sign before the year value represents BC.

Finally, you can have some fun with using another language for your output. Enter and execute the code in Listing 7.10 for an example of German output.

INPUT
Listing 7.10. Converting DATE to another language.

SELECT TO_CHAR(SYSDATE,'MONTH DD YY','NLS_DATE_LANGUAGE=german')
      "German Date" from DUAL;

OUTPUT
Your output should appear as

German Date
------------------
JUNI      04 97

Using TO_NUMBER

The TO_NUMBER function is very similar to the TO_DATE function. This function converts a character string of type CHAR or VARCHAR2 into a number. As with TO_DATE, the format mask is very important for a proper conversion.

The Syntax for the TO_NUMBER Function

TO_NUMBER(character_string, format, NLS_Params)

There are many reasons to convert from a character to a number value. For instance, you decide to store data of type VARCHAR2 for the age when hired. Suppose you want to perform some calculations on the age to determine retirement income and information. Simply use the TO_NUMBER function to change to a NUMBER datatype and then perform the calculation. It's more efficient to store numbers in the CHAR or VARCHAR2 format because most systems will store as a single byte instead of two bytes with a NUMBER datatype, and you would not perform calculations very often on this data.

See Table 7.8 for the available format masks you can pass when using the TO_NUMBER function.

Table 7.8. Number format elements.