To format a Teradata DATE as text, use TO_CHAR. To convert text into a DATE, use CAST with an explicit input format or TO_DATE. These operations solve different problems: a DATE value does not store the slashes, dashes or month names you see on screen.
Watch: Teradata date formats in 4 minutes
This video shows the conversions from this article in action: why a DATE has no format, only a display, when to use TO_CHAR or a nested CAST, and how to parse text safely with CAST … FORMAT or TO_DATE.
Verified example: DATE value versus formatted text
Lab verification: 22 October 2026. The example below was executed on Teradata Database 20.00.28.81 using BTEQ 20.00.00.11 in Teradata (BTET) transaction mode. Each SQL statement was submitted as a separate request. These are small functional checks, not performance benchmarks; results involving transaction boundaries can differ in ANSI mode or an explicit transaction.
SELECT
CAST('2026-10-22' AS DATE FORMAT 'YYYY-MM-DD') AS parsed_date,
CAST(CAST(DATE '2026-10-22' AS FORMAT 'DD/MM/YYYY')
AS CHAR(10)) AS formatted_text,
TO_CHAR(DATE '2026-10-22', 'YYYY-MM-DD') AS iso_text;
| Expression result | Observed BTEQ output | Meaning |
|---|---|---|
| parsed_date | 2026-10-22 | A DATE value; its display can depend on your client. |
| formatted_text | 22/10/2026 | A CHAR(10) value with explicit output characters. |
| iso_text | 2026-10-22 | Text returned by TO_CHAR. |
The two ISO-looking results have different types. Keep dates as DATE values for comparisons, joins and arithmetic; format them as text at the output boundary. When preparing a temporary transformation, use a volatile table and retain the original typed date alongside any display text.
Teradata date formats: quick reference
| Task | Expression | Result |
|---|---|---|
| DATE to ISO text | TO_CHAR(DATE '2026-10-22', 'YYYY-MM-DD') | 2026-10-22 (text) |
| DATE to day/month/year text | TO_CHAR(DATE '2026-10-22', 'DD/MM/YYYY') | 22/10/2026 (text) |
| DATE to compact text | TO_CHAR(DATE '2026-10-22', 'YYYYMMDD') | 20261022 (text) |
| Text to DATE | CAST('22/10/2026' AS DATE FORMAT 'DD/MM/YYYY') | 22 October 2026 (DATE) |
| Compact text to DATE | TO_DATE('20261022', 'YYYYMMDD') | 22 October 2026 (DATE) |
The examples use fixed dates so you can compare results. A client can display a DATE differently from the text shown here. If the exact output characters matter for a file or interface, return a character value explicitly.
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 bookFormat a DATE as YYYY-MM-DD, DD/MM/YYYY or YYYYMMDD
SELECT
TO_CHAR(DATE '2026-10-22', 'YYYY-MM-DD') AS iso_date,
TO_CHAR(DATE '2026-10-22', 'DD/MM/YYYY') AS european_date,
TO_CHAR(DATE '2026-10-22', 'YYYYMMDD') AS compact_date;
Expected text values: 2026-10-22, 22/10/2026 and 20261022. For a column, replace the date literal with the column name:
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS order_date_text
FROM orders;
The column example assumes that orders.order_date is a DATE. Use MM/DD/YYYY for US-style output. Month-name formats can depend on language settings; numeric months are usually a clearer choice for data exchange. See the broader Teradata TO_CHAR guide for other conversions.
Convert a string to DATE with CAST or TO_DATE
SELECT
CAST('22/10/2026' AS DATE FORMAT 'DD/MM/YYYY') AS parsed_date,
TO_DATE('20261022', 'YYYYMMDD') AS compact_input_date;
Both expressions represent 22 October 2026. The format describes the incoming text, not how the resulting DATE must appear in SQL Assistant. A string such as 03/04/2026 is ambiguous until you specify whether the first component is the day or the month.
For a fixed date in SQL, prefer an ANSI date literal:
SELECT DATE '2026-10-22' AS example_date;
Use four-digit years. A format model cannot make an invalid calendar date valid: 31/02/2026 is still invalid. Validate source data rather than trying different formats until a conversion happens to succeed.
Use FORMAT and CAST when you need explicit character output
A FORMAT attribute alone does not change a DATE into text. When using Teradata FORMAT syntax for an export, perform the character conversion explicitly:
SELECT CAST(
CAST(DATE '2026-10-22' AS DATE FORMAT 'DD/MM/YYYY')
AS CHAR(10)
) AS date_text;
Expected text: 22/10/2026. Allocate enough characters for the entire result. TO_CHAR format models and Teradata FORMAT phrases are separate formatting systems; do not assume every token is interchangeable.
Format timestamps and time values
SELECT TO_CHAR(
TIMESTAMP '2026-10-22 14:05:09',
'YYYY-MM-DD HH24:MI:SS'
) AS timestamp_text;
Expected text: 2026-10-22 14:05:09. In this TO_CHAR model, HH24 is the 24-hour clock, MI means minutes, and MM means month. Use HH24:MI:SS when you only need the time portion. A format without fractional seconds or a time-zone component omits that information from the text; decide whether the receiving system needs it.
Extract date parts and keep calculations typed
SELECT
EXTRACT(YEAR FROM DATE '2026-10-22') AS date_year,
EXTRACT(MONTH FROM DATE '2026-10-22') AS date_month,
EXTRACT(DAY FROM DATE '2026-10-22') AS date_day;
Expected numeric values: 2026, 10 and 22. Use DATE values for comparisons and arithmetic, then format the final output. For a DATE column, a month filter can be written as:
SELECT order_date
FROM orders
WHERE order_date >= DATE '2026-10-01'
AND order_date < DATE '2026-11-01';
This avoids wrapping the filtered column in a text-formatting function. Whether the optimizer can eliminate partitions or use another access path still depends on the table design and query plan. For date arithmetic, see Teradata date calculations.
Why SQL Assistant displays a different date format
The database type, its format attributes and the client/provider display settings are different layers. A DATE can appear differently in two clients while representing the same calendar day. Compare a DATE expression with TO_CHAR output to distinguish a conversion problem from a display setting. The SQL Assistant connection and CSV export guide explains the client side.
| Symptom | Check first |
|---|---|
| Invalid date during conversion | Match the format to the input and check for impossible dates, whitespace and mixed source formats. |
| Day and month are swapped | Specify DD/MM/YYYY or MM/DD/YYYY explicitly; avoid ambiguous input. |
| FORMAT appears to be ignored | Check the returned data type and client display settings. Use TO_CHAR or an explicit character cast for text output. |
| Minutes are wrong in timestamp output | Use MI for minutes in a TO_CHAR format model, not MM. |
| CSV looks correct but Excel changes it | Inspect the raw file and import the column with an explicit type instead of relying on automatic conversion. |
Documentation and related reading
Teradata documents explicit FORMAT phrases for character-to-DATE conversion. Check the reference for your database release when using less common format elements. Continue with the TO_CHAR reference or the Teradata SQL guide.
Reproduce the lab checks
Download the SQL used for these functional checks. Run it in a separate BTET lab session with unused dwhpro_ai_* table names. It creates and drops only its own volatile tables and reads DBC metadata; it contains no login credentials. Execute the statements separately, as in the test. The file covers date formatting, duplicate rows, commit behaviour, table copying and spool counters.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
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