Example - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™

Field T1 in the table INTTIME is a TIME(6) value with the explicit format ' HH:MI:SSDS(6) '. Assume that you want to convert this to a value of CHAR(6), and an explicit output format of ' HHhMIm '.

SELECT T1 FROM INTTIME ;

The result (without a type change) is the following report:

             T1
---------------
05:57:11.362271

Now use nested CAST phrases and a FORMAT to obtain the desired result: a report in character format.

SELECT
CAST( (CAST (T1 AS FORMAT 'HHhMim'))
AS CHAR(6))
FROM INTTIME;

The result after the nested CASTs is the following report.

T1
------
05h57m

The inner CAST establishes the display format for the TIME value and the outer CAST indicates the data type of the desired result.