The Teradata TO_CHAR Function
Utilize the Teradata TO_CHAR function to:
Part of the Teradata Statistics and Cardinality Estimation guide.
- Convert numeric expressions to character strings
- Convert dates, times, interval data types, or timestamps to character strings
This is an example of a numeric expression argument, which can be any of the following data types: BYTEINT, SMALLINT, INTEGER, BIGINT, DECIMAL/NUMERIC, FLOAT/REAL/DOUBLE PRECISION, or NUMBER.
The format string, which must be of type CHAR or VARCHAR, represents the second argument (Further information on format strings can be found in the Teradata documentation: link).
SELECT TO_CHAR(123456789,'999999999');
If the query returns only ‘#’ characters, it indicates a conversion failure.
SELECT TO_CHAR(123456789,'99');
--> ###
An example of a date expression argument is provided below. The first argument must be a valid data type, including DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or an Interval data type. The second argument must be either CHAR or VARCHAR.
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 bookSELECT TO_CHAR(DATE'2019-11-09', 'DD-MON-YYYY');
--> '09-NOV-2019'
Teradata TO_CHAR and Statistic Estimations
Teradata’s frequent release of new features is exciting, as it simplifies our tasks. Nevertheless, it is crucial to remember that functions may lack statistics, posing a risk to the optimizer.
This example illustrates such a case. Initially, we will utilize TO_CHAR on a date and inspect the execution plan.
SELECT Col_1, Col_2 FROM
DBNAME.TableName
WHERE TO_CHAR(Date_Col, 'mm-dd-yyyy') ='12-31-9999'
ORDER by 1;
COLLECT STATS COLUMN TO_CHAR(Date_Col, 'mm-dd-yyyy') AS COL_DATE_UDF
ON DBNAME.Tablename;
The execution plan shows that in step 2, the optimizer lacks confidence despite having collected statistics on the WHERE condition column. The estimated number of rows is incorrect at 1, whereas the result is two.
Step 2:
Next we do an all-amp operations RETRIEVE step from dbname.tablename in view viewed.view_nm by way of an all row scan with a condition of "(TD_SYSFNLIB.To_Char(CAST((date_col in view viewing.viewnam. Date_Col) as TIMESTAMP(6) WITH TIME ZONE) AT TIME ZONE INTERVAL '0:00' HOUR TO MINUTE, 'mm-dd-yyyy')) ='12-31-9999'") into Spool 1 (group_amps). which is built locally on the amps. Then we do a SORT to order Spool 1 by the sort key in spool field1. The Size of spool 1 is estimated with no confidence to be 1 row.
Rewrite the query using a CAST function.
SELECT Col_1, Col_2 FROM
DBNAME.TableName WHERE CAST(CAST(Date_Col as DATE FORMAT 'MM-DD-YYYY') as CHAR(10)) ='12-31-9999' ORDER by 1;
COLLECT STATS COLUMN CAST(CAST(Date_Col as DATE FORMAT 'MM-DD-YYYY') as CHAR(10)) as COL_DATE_UDF on DBNAME.Tablename;
-- The former after-CAST plan repeated TO_CHAR and is not valid comparison evidence.
-- Capture a fresh EXPLAIN for the actual typed-date or CAST predicate.
TO_CHAR is a built-in function, not an arbitrary user-authored UDF. Functions can change how statistics apply, but do not always prevent their use. Prefer a typed DATE predicate when equivalent, and preserve timestamp/time-zone and truncation semantics.
Conclusion
For common date conversions with fixed inputs and expected results, see the Teradata date-format reference. It also explains why a DATE can display differently from a character string in SQL Assistant.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
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
Thank you for this great post!
Roland
This is a fine example of why end-users should be trained to write good SQL 🙂
Instead of an explicit CAST/TO_CHAR one could simply use
WHERE ENT_END_TD = DATE ’9999-12-31′
This automatically casts the Timestamp to a Date.
And changing the WHERE into a sargable condition would be even better:
WHERE ENT_END_TD BETWEEN CAST(DATE ‘9999-12-31’ AS TIMESTAMP)
AND TIMESTAMP ‘9999-12-31 23:59:59.999999’
If it wasn’t for 9999-12-31 I would prefer:
WHERE ENT_END_TD >= CAST(DATE ‘2016-01-08’ AS TIMESTAMP)
AND ENT_END_TD < CAST(DATE '2016-01-08' +1 AS TIMESTAMP)
Thank you, Dieter, agree with you, thank you again.