DWHPro Insights

Teradata Built-In Functions: TO_NUMBER, TRUNC, CEILING, FLOOR, LEAST, GREATEST, ROUND

By Roland Wenzlofsky · Published · Updated
Tested on Teradata 20.00.28.81 · numeric conversion and rounding examples

Teradata Built-In Functions: TO_NUMBER, TRUNC, CEILING, FLOOR, LEAST, GREATEST, ROUND

Use TO_NUMBER to parse numeric text, TRUNC to discard decimal digits, and ROUND to round them. FLOOR and CEILING move toward negative and positive infinity respectively. Those differences matter for negative values. A letter-case comparison is not a numeric validator.

Lab check: 24 October 2026 · Teradata 20.00.28.81 · BTEQ 20.00.00.11 · BTET. The values below were evaluated in the lab; decimal display padding can vary by client. Download the SQL and observed results.

Parse numbers without confusing invalid input and missing data

SELECT TO_NUMBER('12.77') AS parsed_number,
       CASE WHEN TO_NUMBER('not-a-number') IS NULL THEN 1 ELSE 0 END AS invalid_is_null,
       CASE WHEN TO_NUMBER(CAST(NULL AS VARCHAR(12))) IS NULL THEN 1 ELSE 0 END AS source_null;

Observed: 12.77, 1, 1. Conversion failure and an already-NULL input both produced NULL. Check the original input separately if your pipeline must distinguish missing values from malformed text. Teradata documents that invalid format arguments can raise an error; a NULL conversion result is not a guarantee that every call is error-free.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

The older UPPER(value) = LOWER(value) shortcut was removed: punctuation can satisfy it without representing a number. Choose an explicit format when parsing locale-specific separators, and test it against representative input.

TRUNC, FLOOR, CEILING and ROUND: tested negative-value examples

SELECT TRUNC(CAST(-12.67 AS DECIMAL(8,2))) AS truncated,
       FLOOR(CAST(-12.67 AS DECIMAL(8,2))) AS floored,
       CEILING(CAST(-12.67 AS DECIMAL(8,2))) AS ceiling_value,
       ROUND(CAST(12.1266 AS DECIMAL(10,4)),2) AS rounded;
ExpressionObserved numeric result
TRUNC(-12.67)-12
FLOOR(-12.67)-13
CEILING(-12.67)-12
ROUND(12.1266,2)12.13

LEAST and GREATEST

LEAST(12.50, 1.00, 2.88) selects the minimum, 1.00; GREATEST(12.50, 1.00, 2.88) selects the maximum, 12.50. These illustrative expressions are separate from the executed test set above. Check NULL handling and compatible argument types when adapting them.

Common numeric-function mistakes

  • Using FLOOR as a synonym for TRUNC on negative numbers.
  • Treating a NULL conversion as proof that the source was malformed rather than absent.
  • Relying on implicit character-to-number conversion without defining separators and precision.
  • Assuming display formatting changes the stored numeric value.

Reference: Teradata TO_NUMBER syntax, conversion behavior and format rules. Continue with Teradata data types and date formatting and conversion.

How should a load distinguish invalid numeric text from NULL?

Retain the original value and classify source NULL separately from a non-NULL value whose conversion returns NULL. Preserve rejected text for an authorized data-quality review. Decide explicitly how to handle blank strings, thousands separators and decimal separators; one successful conversion does not validate every locale.

Why do FLOOR and TRUNC disagree on negative values?

FLOOR moves toward negative infinity, whereas TRUNC removes fractional digits toward zero. The tested -12.67 example therefore produces -13 with FLOOR and -12 with TRUNC. Choose based on the business rule rather than treating the functions as interchangeable.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam