Examples: Using Time Formats - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

Create a table using the FORMAT phrase to specify the output format:

CREATE TABLE t1
(f1 TIME(3) WITH TIME ZONE FORMAT 'HH:MI:SS.S(F)'
,f2 TIMESTAMP(4) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z');

Populate the table using TIME and TIMESTAMP literals.

INSERT t1 (TIME '10:44:25.123-08:00',
TIMESTAMP '2000-09-20 10:44:25.1234');

Query the data:

SELECT f1, f2
FROM t1;

The query returns:

          f1                       f2
------------ ------------------------
10:44:25.123 2000-09-20 10:44:25.1234

Column f1 was defined as a TIME WITH TIME ZONE data type, but the FORMAT did not include the Z formatting character, so the time zone does not appear in the output. Column f2 was defined as a TIMESTAMP data type, and the FORMAT includes the Z formatting character. However the timestamp data is not associated with any time zone, so no time zone information appears in the output.

To display the time zone information for column f1, use the FORMAT phrase in the SELECT:

SELECT CAST (f1 AS TIME(3) WITH TIME ZONE FORMAT 'HH:MI:SS.S(F)Z')
FROM t1;

The query returns:

               f1
-----------------
10:44:25.123-8:00

For more information on TIME and TIMESTAMP literals, see Data Literals.

For more information on using the FORMAT phrase in data type conversions, see Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates, B035-1145.