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...