Conversion Functions and Element Extraction

So far, we have focused on the methods of DATE and TIMESTAMP value construction, delimited by the American National Standards Institute (ANSI) standardization and by using the TO_DATE and TO_TIMESTAMP functions. TO_DATE and TO_TIMESTAMP functions can also be used robustly for conversions and element extraction. This chapter summarizes the principles of these methods by focusing on the parameters and mapping formats. It also explains the extraction functions.

First, the TO_CHAR function is described. It produces a character string as a result, commonly defined by the specified format mapping. This function should be used, although the Oracle Database offers implicit conversions of the data types. Controlled transformation is always preferred, while implicit conversions can have a significant impact on performance. Second, the century reference is discussed. Namely, if the value of the year takes only two digits, the system must infer the...

Understanding the TO_CHAR and TO_DATE conversion functions

Each Date or Time value consists of individual elements to be handled. The format must be explicitly specified during the value construction, or the ANSI norm must be applied to ensure correct mapping and evaluation. Date and Time values can be composed of either character strings or numerals, depending on the context. Similarly, getting individual elements from a particular value can be done using a numerical or textual representation. The TO_CHAR and TO_DATE functions ensure the core functionality of obtaining elements and transforming values. As evident from the name of the function itself, the transformation from a string or number to a date is performed by the TO_DATE function. To obtain individual elements from the date, the TO_CHAR function is generally used. These functions behave similarly. Using them in a common structure, the value itself is defined in the first parameter. The second parameter determines the structure...

Understanding the EXTRACT function

The EXTRACT function of the Oracle DBS is used to access only one element from the DATE or TIMESTAMP value. It can extract date elements (day, month, and year) that both the DATE and TIMESTAMP values can apply to. Time elements (hour, minute, and second) and time zone extensions (timezone_hour, timezone_minute, timezone_region, and timezone_abbreviation) can be applied only to TIMESTAMP values. Even in the Oracle Cloud 21c release version, time elements cannot be obtained by the EXTRACT function for DATE. So, in this specific situation, DATE values cannot be implicitly converted to TIMESTAMP. The syntax is as follows:

EXTRACT <field> FROM <source>

Here, the field value of EXTRACT can be day, month, year, hour, minute, second, timezone_hour, timezone_minute, timezone_region, or timezone_abbreviation. The following select statements express the principles by taking day and hour elements from the provided values:

select EXTRACT(day...

Reliability and integrity issues

It is important to comply with this rule: never rely on implicit conversions, and never treat Date and Time values as character strings by applying the SUBSTR function. By changing the session format, the provided input value does not need to fit the mapping. Imagine a system migration to another server or the cloud. The original formats and assumed parameter values would not be retained. It can result in obtaining an improper value (such as changing the value of month and day, if applicable) or even raising an exception. Thus, it would be necessary to revise and rebuild the whole application! Avoid this situation before it occurs! It is enormously time- and money-consuming and requires the involvement of many developers. In addition, after the process, the whole system must be retested.

Thus, extraction using a substring is insufficient. If the session date format is changed, the extracted value does not need to be relevant. The developer cannot...

Investigating the CAST function

The CAST function gets the input value and converts it into the specified data type in the definition. The input value can be provided by the attribute, function result, or expression, or a NULL value can be used. A CAST function can be used where expressions are permitted, using the following syntax:

CAST ( {<expression> | NULL } as <output_datatype> )

In this section, we will explain the DATE and TIMESTAMP data types, which originated from the character string and numerical values. We will focus on the transformations across all data types. Finally, there will be a note related to the undefined value, NULL.

Casting a character string to a DATE value

An example of the usage of the CAST function is reflected in the following code snippet. In the CAST function, a character string is taken by providing the DATE value as the output. The first element is assumed to represent the day followed by the month and year specification. Thus...

Validating conversions

The data to be stored in the database can originate from various sources. For example, by loading the values from CSV files, a textual representation is present, requiring the system to convert the data to the proper format and data type. Thus, before processing and storing values, it is always necessary to ensure the value can be mapped to the desired data type. Moreover, for the date and time values, the format mask (mapping) must also be applicable. To test the conversion opportunities, instead of hardcoding using PL/SQL and exception handling, the validate_conversion function, introduced by the Oracle database, can be used. It takes an expression and the output data type (output_data_type), optionally extended by the format mask (format_mapping) and NLS parameters (nls_parameters) for the date and time values. The syntax of the validate_conversion function is shown in the following code block:

validate_conversion 
  (
    ...

Summary

In this chapter, you learned about the TO_DATE, TO_TIMESTAMP, and TO_CHAR conversion functions, with the available formats being highlighted. They can reference any elements and formats, with precision ranging from the day to fractions of a second. Century reference rules were described, including when the year is specified by just two digits and the century assignment can be calculated by the system.

You saw how the opposite of the TO_CHAR function is covered by the supplementary EXTRACT function, which can take only one element.

Finally, you learned about the reliability issues if date and time values are treated as a character string. From the visual perspective, the date and time values seem to be represented textually, right? However, that does not mean we should treat these values as strings, because they strongly depend on the format, which can be changed on the client and server sides anytime. Moreover, by migrating the solution to another server, different rules...

Questions

1. Which format displays the number of the month in Roman numeral format?
   1. MM
   2. RM
   3. MR
   4. MON
2. Which parameter mapping value provides you with the name of the day in a textual format?
   1. DAY
   2. DY
   3. D
   4. DDD
3. Which parameter expresses the output of the hour extraction in 24-hour format?
   1. HH
   2. HH12
   3. HH24
   4. HHEurope
4. Which data type can be specified as the source of the EXTRACT function?
   1. DATE only
   2. TIMESTAMP only
   3. INTERVAL only
   4. DATE, TIMESTAMP, and INTERVAL

Further reading

• Datatype conversions – strange internal function by Connor McDonald. This is a practical guide dealing with implicit conversions and related performance aspects. It can be found at https://connor-mcdonald.com/2021/01/27/datatype-conversions-strange-internal-function/:

• When Implicit Date Conversions Attack by the Oracle-Base team.
• This paper deals with the consequences of implicit date conversions and cascading. It can be found at https://oracle-base.com/blog/2020/07/08/when-implicit-date-conversions-attack/: