Always use the TO_NUMBER function if you need to convert characters to numbers for computations or for changing datatypes. The next section explains the inverse function TO_CHAR.

Using TO_CHAR for Numbers

Once again, if you can change characters to numbers, you should also be able to change numbers to characters by using the function TO_CHAR.

TO_CHAR(number, format, NLS_Params)

The format mask and the NLS parameters are identical to the TO_NUMBER function. You can review the format masks in Table 7.8. The NLS parameters again are

• NLS_NUMERIC_CHARACTERS—Specifies characters to use for group separators and the decimal point.
• NLS_CURRENCY—Specifies the local currency.
• NLS_ISO_CURRENCY—Character(s) to represent the ISO currency symbol.

You can now make some decent format attempts for numbers in a column. You will print out an employee ID number with leading zeros and a total length of 10 characters. Go ahead and try the PL/SQL code in Listing 7.15.

INPUT
Listing 7.15. Converting number to character formatted as a numeric string.

 1: DECLARE
 2:      v_Convert_Number NUMBER := 90210;
 3:      v_Hold_Char VARCHAR2(21) ;
 4: BEGIN
 5:      v_Hold_Char := TO_CHAR(v_Convert_Number,'0000000000');
 6:      DBMS_OUTPUT.PUT_LINE(`The employee ID is: ` || v_Hold_Char);
 7: END;
 8: /

OUTPUT
After executing the PL/SQL code block, your output should appear as

The employee ID is:  0000090210

You were able to take a five-digit number, or any NUMBER with a value of 10 digits as specified by the mask in line 5, and pad the number to the left with zeros. The number 90210 becomes 0000090210 because you use 0s for the format mask, which means that zeros should be output instead of blanks.

You can now look at some other types of NUMBER formatting. Suppose you are asked to do some work for engineers or scientists. They require you to express the results in scientific notation. This is simply the number converted to however many significant digits to the nth power of 10. The number 1000 would be 1.00E+03 because you would shift the decimal point to the left three places. You can practice this in Listing 7.16.

INPUT
Listing 7.16. Expressing your work in scientific notation.

 1: DECLARE
 2:      v_Convert_Number NUMBER := 90210;
 3:      v_Hold_Char VARCHAR2(21) ;
 4: BEGIN
 5:      v_Hold_Char := TO_CHAR(v_Convert_Number,'9.99EEEE');
 6:      DBMS_OUTPUT.PUT_LINE(`The Scientific Notation is: ` || v_Hold_Char);
 7: END;
 8: /

OUTPUT
Your output would appear as

The Scientific Notation is: 9.02E+04

Again, 90210 would allow you to shift the decimal point to the left four spaces. Because you are taking only two significant digits after the decimal point, the result is 9.02E+04. Go ahead and practice formatting numbers. When you are finished, you can continue with performing calculations on dates!

DATE Functions

Oracle provides several built-in date functions to perform many complex date calculations. Oracle holds the true system DATE in the format DD-MM-YYYY to handle all dates from January 1, 4712 BC to December 31, 4712 AD. By that time you will be working with Oracle version 5000! In addition, Oracle holds the true system TIME in the format HH-MM-SS in 24-hour military format.

SYSDATE

The SYSDATE function returns the current date and time in the Oracle server. Note the distinction that it is the server and not the client's date and time that is being returned. The format for the SYSDATE function is

SYSDATE

That's it! Not only can you get the system DATE and TIME from Oracle, but also you can format it in any way possible, and you can perform calculations on the system DATE and TIME. Refer

to Table 7.7 for possible formats. You can now begin with the standard output from using SYSDATE from Listing 7.17.

INPUT
Listing 7.17. Default output of SYSDATE.

SELECT SYSDATE from DUAL;

OUTPUT
Your output should appear similar to

SYSDATE
--------
01-JUN-97

Don't forget that SYSDATE is not a variable but a function that retrieves the date and time from the server. You can add the TO_CHAR function to format the system date and time to something you are more familiar with, as shown in Listing 7.18.

INPUT
Listing 7.18. Combining TO_CHAR to format SYSDATE.

SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MM:SS AM')
      "Today's Date and Time" from DUAL;

OUTPUT
A similar output should look like

Today's Date and Time
------------------------------
06/01/1997 11:06:21 PM

As you can see from the time, the standard programming time of all hours of the night still exists! In the rest of this section, you can practice some of the other built-in DATE functions.

The TRUNC Function

The TRUNC function is useful for returning a truncated DATE or TIME to a specified mask. For instance, you can truncate to the nearest day, month, quarter, century, and so on. The main use of TRUNC is to simply eliminate the time from the SYSDATE by setting all time values for all dates to 12:00 a.m.

The Syntax for the TRUNC Function

TRUNC(date_passed,truncate mask)

In order to understand the TRUNC function, see Table 7.9, which provides a list of possible values to use with the TRUNC and ROUND functions.

Table 7.9. Masks used with the ROUND and TRUNC functions.

Now that you have seen all the possible masking options, try out how the TRUNC function operates by testing it with different examples. You will first truncate the time from the system date. Remember, you will still see the time displayed, but if you use TRUNC on all dates, the time will always be 12:00 a.m. instead of the time the date was assigned; therefore, all dates can be calculated properly regardless of time. Go ahead and execute the SQL code in Listing 7.19.

INPUT
Listing 7.19. Removing the time from SYSDATE.

1: SELECT TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH:MM:SS AM')
2:     "Today's Date and Time"
3:      from DUAL;

OUTPUT
Your output will appear similar to

Today's Date and Time
----------------------
06/01/1997 12:00:00 AM