| In the real world, temporal data are not always represented using Oracle's DATE, TIMESTAMP, and INTERVAL data types. At various times, you'll need to convert temporal values to other data types, especially to character types, and vice versa. This is particularly true when you interface an Oracle database with an external system, for example when you are accepting date input from an external system in which dates are represented as strings of characters (or even as numbers), or when you are sending output from an Oracle database to another application that doesn't understand Oracle's native temporal data types. You also need to convert DATE and TIMESTAMP values to text when you display them on a screen or generate a printed report. Oracle provides some extremely useful functions to enable such conversions: TO_DATE TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVAL TO_DSINTERVAL NUMTOYMINTERVAL NUMTODSINTERVAL TO_CHAR
The purpose of each of these functions is more or less self-explanatory. The following sections discuss each of these functions in detail. 6.4.1 TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ are built-in SQL functions that convert, respectively, a character string into a DATE, a TIMESTAMP, and a TIMESTAMP WITH TIME ZONE. Input to these functions can be string literals, PL/SQL variables, and database columns of the CHAR and VARCHAR2 data types. These three conversion functions are similar in operation. The difference is only in the data type of the return value. You call them as follows: TO_DATE(string [,format]) TO_TIMESTAMP (string [,format]) TO_TIMESTAMP_TZ (string [,format])
The syntax elements are:
- string
-
Specifies a string literal, a PL/SQL variable, or a database column containing character data (or even numeric data) convertible to a date or timestamp.
- format
-
Specifies the format of the input string. The format must be a valid combination of format codes shown in Table 6-1, which you'll find later in Section 6.5.
Through the format argument, Oracle provides a great deal of flexibility when it comes to converting between date and time values and text. Oracle provides far more flexibility, at least in our experience, than do competing platforms, such as DB2 and SQL Server. Specifying a format is optional. When you don't specify a format, the input string is assumed to be in a default format as specified by the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ parameter settings.  | You can view your current NLS parameter settings by querying the view named NLS_SESSION_PARAMETERS. |
|
6.4.1.1 Using the default formats Every Oracle session has a set of default formats to use in converting date and timestamp values to and from their textual representations. You can query the NLS_SESSION_PARAMETERS view as follows to see the default formats currently in effect: SELECT parameter, value FROM nls_session_parameters WHERE parameter LIKE '%FORMAT'; PARAMETER VALUE ------------------------------ ------------------------------ NLS_DATE_FORMAT DD-MON-RR NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
Session-specific formats derive from settings for language and territory. If you connect without specifying a language and territory, your session will inherit the default conversion formats established for the database. You can query NLS_DATABASE_PARAMETERS for those. When you invoke one of the TO_ conversion functions, say TO_DATE, without explicitly specifying a format, Oracle expects your input string to be in the default format for the target data type. The following INSERT statement converts a string in the default date format into a DATE, which is then inserted into the employee table: INSERT INTO employee (emp_id, fname, lname, dept_id, manager_emp_id, salary, hire_date) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE('22-OCT-99')); 1 row created. SELECT * FROM employee; EMP_ID FNAME LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE ------- -------- ------- ---------- -------------- ---------- --------- 2304 John Smith 20 1258 20000 22-OCT-99
The hire_date column, into which our date was inserted, is of type DATE. Because the input character string of '22-OCT-99' matched Oracle's default date format, the string could be converted without reference to a format string. In fact, since the supplied string is in the default date format, you don't even need the TO_DATE function. Oracle automatically performs an implicit type conversion, as in this example: INSERT INTO employee (emp_id, fname, lname, dept_id, manager_emp_id, salary, hire_date) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, '22-OCT-99'); 1 row created.
Even though Oracle provides means for implicit data type conversions, we recommend always using explicit conversions, because implicit conversions are not obvious and may lead to confusion. They may also suddenly fail should a DBA change the database's default date format. 6.4.1.2 Specifying a format If you wish to specify a format to use in converting from text to one of the temporal data types, there are at least two approaches you can take: Specify the format at the session level, in which case it applies to all implicit conversions, and to all TO_DATE, TO_TIMESTAMP, or TO_TIMESTAMP_TZ conversions for which you do not explicitly specify some other format. Specify the format as a parameter in a TO_X function call.
The following example changes the default date format for the session, and then uses TO_DATE to convert a number to date: ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYY'; Session altered. INSERT INTO employee (emp_id, fname, lname, dept_id, manager_emp_id, salary, hire_date) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE(102299)); 1 row created.
Since the default date format has been changed prior to the conversion, the conversion function TO_DATE doesn't need the date format as an input parameter.  | Although it is possible to pass a number such as 102299 to the TO_DATE function, relying on Oracle's implicit conversion to change the number to a string, and then into a date, it's probably best to pass a string as input to the TO_DATE function. |
|
If you do not wish to change your session's default date format, you must specify the date format as the second input parameter to whichever of the three functions you are using. For example, the following SELECT specifies a format as the second input parameter to the TO_TIMESTAMP_TZ function: SELECT TO_TIMESTAMP_TZ('12/10/01 08:15:00.50 EST','MM/DD/YY HH:MI:SSXFF TZR') FROM DUAL; TO_TIMESTAMP_TZ('12/10/0108:15:00.50EST','MM/DD/YYHH:MI:SSXFFTZR') --------------------------------------------------------------------------- 10-DEC-01 08.15.00.500000000 AM EST
Let's look at one more example to see how a database character column can be converted to a TIMESTAMP. Let's assume that the report_id column in the report table actually stores the date on which the report was generated, and that the date is in the format "MMDDYYYY." Now, you can use TO_TIMESTAMP on that column to convert that date into a TIMESTAMP, which is then displayed using the default timestamp format: SELECT sent_to, report_id, TO_TIMESTAMP(report_id,'MMDDYYYY') date_generated FROM report; SENT_TO REPORT_I DATE_GENERATED -------------------- -------- --------------------------------- Manager 01011999 01-JAN-99 12.00.00.000000000 AM Director 01121999 12-JAN-99 12.00.00.000000000 AM Vice President 01231999 23-JAN-99 12.00.00.000000000 AM
In this example, the TO_TIMESTAMP function converts the MMDDYYYY data in the column to a TIMESTAMP. That TIMESTAMP value is then implicitly converted into a character string for display purposes, using the default timestamp format. | Later, in the section on the TO_CHAR function, you'll learn how you can use formats to exercise great control over the textual representation of date and timestamp values. |
|
6.4.1.3 Converting to TIMESTAMP WITH LOCAL TIME ZONE Interestingly, Oracle provides no function specifically to convert a text value into the TIMESTAMP WITH LOCAL TIME ZONE data type. To convert a value to TIMESTAMP WITH LOCAL TIME ZONE, you must use the CAST function, as in the following example: SELECT CAST('10-DEC-01' AS TIMESTAMP WITH LOCAL TIME ZONE) FROM DUAL; CAST('10-DEC-01'ASTIMESTAMPWITHLOCALTIMEZONE) --------------------------------------------------------------------------- 10-DEC-01 12.00.00 AM
In this example, the input string is in the default date format. Therefore, no date format is required for conversion. Indeed, CAST does not support date formats. What then do you do if you wish to convert to TIMESTAMP WITH LOCAL TIME ZONE and you also need to specify a format? One solution here is to use a conversion function along with a format to convert the string into a value TIMESTAMP WITH TIME ZONE, which you can then cast to a TIMESTAMP WITH LOCAL TIME ZONE: SELECT CAST(TO_TIMESTAMP_TZ('12/10/01','MM/DD/YY') AS TIMESTAMP WITH LOCAL TIME ZONE) FROM DUAL; CAST(TO_TIMESTAMP_TZ('12/10/01','MM/DD/YY')ASTIMESTAMPWITHLOCALTIMEZONE) --------------------------------------------------------------------------- 10-DEC-01 12.00.00 AM
The CAST function used in these examples is not a SQL function in the truest sense. CAST is actually a SQL expression like DECODE and CASE. The CAST expression converts a value in one data type to a value in another data type. You can generally CAST between any two, compatible data types. 6.4.2 TO_YMINTERVAL and TO_DSINTERVAL The TO_YMINTERVAL and TO_DSINTERVAL functions are similar in purpose to the TO_DATE family of functions, and serve to convert character strings to the INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND data types. You can pass literals, PL/SQL variables, and database columns of CHAR or VARCHAR2 data type to these functions, which you invoke as follows: TO_YMINTERVAL (string) TO_DSINTERVAL (string)
In these invocations, string must contain character data convertible to an INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND value, and in one of the following formats:
- TO_YMINTERVAL
-
The input string must be in Y-M format i.e., the year and month values must be separated by a dash (-). All components (year, month, and -) must be present in the string.
- TO_DSINTERVAL
-
The input string must be in D HH:MI:SS format. The day value of the interval is separated by a space from the time value, which is expressed in hours, minutes, and seconds, and is delimited by ":". All components must be present in the string for it to be converted to an INTERVAL DAY TO SECOND value.
The following two INSERT statements demonstrate the use of these functions: INSERT INTO event_history VALUES (5001, TO_YMINTERVAL('02-04')); INSERT INTO batch_job_history VALUES (6001, TO_DSINTERVAL('0 2:30:43'));
In this example, the string '02-04' represents an interval of 2 years and 4 months, while the string '0 2:30:43' represents an interval of 0 days, 2 hours, 30 minutes, and 43 seconds. 6.4.3 NUMTOYMINTERVAL and NUMTODSINTERVAL The NUMTOYMINTERVAL (NUMBER-TO-YEAR-MONTH-INTERVAL) and NUMTODSINTERVAL (NUMBER-TO-DAY-SECOND-INTERVAL) functions convert numeric values into INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND values, respectively. You invoke these functions as follows: NUMTOYMINTERVAL (n, unit) NUMTODSINTERVAL (n, unit)
The syntax elements are:
- n
-
Specifies a numeric value, or a value that is convertible to a numeric type.
- unit
-
Specifies the unit of time that n represents. When converting to an INTERVAL YEAR TO MONTH, unit may be either 'YEAR' or 'MONTH'. When converting to an INTERVAL DAY TO SECOND, unit may be any of 'DAY', 'HOUR', 'MINUTE', or 'SECOND'. Case does not matter. Upper, lower, or mixed-case are all the same.
The following example demonstrates the use of these two functions. The first INSERT specifies an interval of two years, while the second specifies an interval of 5369.2589 seconds: INSERT INTO event_history VALUES (5001, NUMTOYMINTERVAL(2,'YEAR')); INSERT INTO batch_job_history VALUES (6001, NUMTODSINTERVAL(5369.2589,'SECOND'));
Unlike the case with TO_YMINTERVAL and TO_DSINTERVAL, you cannot pass mixed units to these NUMTOXXINTERVAL functions. However, you can build up values from mixed units as follows: INSERT INTO event_history VALUES (7001, NUMTOYMINTERVAL(2,'YEAR') + NUMTOYMINTERVAL (4, 'MONTH'));
This INSERT creates a two-year and four-month interval by adding a two-year interval to a four-month interval. | Remember, that there is a "break" in the interval model between days and months. You cannot add an INTERVAL DAY TO SECOND value to an INTERVAL YEAR TO MONTH value. |
|
6.4.4 TO_CHAR The TO_CHAR function is the opposite of the TO_DATE and TO_TIMESTAMP functions, and converts a DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE value into a string of characters. Call TO_CHAR as follows: TO_CHAR(temporal_data [,format])
The syntax elements are:
- temporal_data
-
Specifies a literal, PL/SQL variable, or a database column of type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE.
- format
-
Specifies the format of the output string. The format must be a valid combination of date or timestamp format elements as described later in Section 6.5.
The format is optional. When the format is not specified, the format of the output depends upon the type of the input data:
- DATE
-
The output string takes the format specified by the parameter NLS_DATE_FORMAT.
- TIMESTAMP
-
The output string takes the format specified by the parameter NLS_TIMESTAMP_FORMAT.
- TIMESTAMP WITH TIME ZONE
-
The output string takes the format specified by the parameter NLS_TIMESTAMP_TZ_FORMAT.
- TIMESTAMP WITH LOCAL TIME ZONE
-
The output string takes the format specified by the parameter NLS_TIMESTAMP_FORMAT.
The database parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT are discussed in Section 6.5.6. The following example uses TO_CHAR to convert an input date into a string using the default date format: SELECT fname, TO_CHAR(hire_date) FROM employee; FNAME TO_CHAR(H -------------------- --------- John 22-OCT-99
The following example uses TO_CHAR to convert a timestamp into a string, and explicitly specifies a timestamp format: SELECT TO_CHAR(SYSTIMESTAMP, 'MM/DD/YYYY HH24:MI:SS.FF') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'MM/DD/Y ----------------------------- 12/12/2003 10:18:36.070000
The format element FF in the preceding example represents fractional seconds. Timestamp-specific formats are discussed in Section 6.5. There are situations when you may need to combine TO_CHAR with TO_DATE. For example, if you want to know on what day of the week January 1, 2000, fell, you can use the following query: SELECT TO_CHAR(TO_DATE('01-JAN-2000','DD-MON-YYYY'),'Day') FROM DUAL; TO_CHAR(T --------- Saturday
In this example, the input string '01-JAN-2000' is first converted into a date and then the TO_CHAR function is used to convert this date into a string representing the day of the week. Printing Numeric Amounts in Words By using the date formats and the functions innovatively, you can generate very interesting and useful outputs. For example, say that you are writing a check-printing application, and you need to print each check amount in words. You can do that using an expression such as in the following SELECT statement: SELECT TO_CHAR(TO_DATE(TRUNC(3456.34),'J'),'Jsp') || ' Dollars and ' || TO_CHAR(TO_DATE(ROUND(MOD(3456.34,1)*100),'J'),'Jsp') || ' Cents' "Check Amount" FROM DUAL; Check Amount ------------------------------------------------------------------- Three Thousand Four Hundred Fifty-Six Dollars and Thirty-Four Cents
This example splits the input number into two components - the first consisting of the whole number and the second consisting of the fractional number. The whole number component is converted to words using the Jsp format. The fractional component is multiplied by 100 and rounded to extract the two digit cents, and then the same technique is applied to convert that number to words. |
|
