LEARN. PRACTICE. GROW.Concept and mechanism
Single-row functions transform values without aggregating the population. SUBSTR selects part of a code, UPPER normalizes case, and numeric functions such as ROUND and TRUNC perform different operations. For NUMBER 12.345, rounding to two places gives 12.35 and truncation gives 12.34. The choice should reflect the calculation rule rather than presentation alone. Implicit conversions can depend on type and NLS settings. For a fixed-format feed, specify the agreed date or numeric model and separators. A value accepted without error may still have been interpreted incorrectly. Do not confuse absence of exceptions with data quality. Keep representative boundary values in the review dataset.
Guided application
In a fictional load, retain source text and rejection reason to enable reconciliation. Use TO_DATE with a mask for fixed contractual dates, TO_NUMBER with explicit numeric configuration, and TO_CHAR when presentation text is needed. Do not apply TO_DATE again to an existing DATE merely to format it. NVL, COALESCE, and NULLIF help handle absence, but replacements need business meaning and compatible types. In CASE, test IS NULL when unknown values need their own category. Replacing every error with zero can turn the process green while changing totals. Support should distinguish invalid format, missing value, and a valid value outside the permitted range.
TO_DATE('2026-09-30','YYYY-MM-DD') parses a fixed contract; NVL(TO_CHAR(amount),'N/A') produces textual presentation.
Common pitfalls
Implicit conversion as contract; NVL as quality repair; rounding as truncation; zero as absence.
Related topics: Model, SELECT, and result population · Joins, grain, and aggregation · Subqueries, sets, and top-N
Make type, format, and the meaning of every replacement explicit.
Reference: Explicit date conversion · 1Z0-071 public objectives inspected 2026-09-30; revision date not published