Datometry Documentation

When CASTing text to a timestamp, you can allow Hyper-Q to accept timestamp values with any character as separators between timestamp parts.

The timestamp must follow the default format of 0000_00_00_00_00_00.0{0,6}, where the underscore (_) represents the separator character and curly braces ({}) represent the range possible millisecond digits, otherwise it is returned as a null value. Examples of accepted timestamps include: '2021-02-03-22.00.00.000000' and '2021g02g03g22g00g00.000000'.

INSERT INTO table_name VALUES ('2021-02-03-22.00.00.000000');

Hyper-Q provides the following error message when the backend database encounters an an unsupported timestamp value.

*** Failure 3706 DTM3103: Failed to run query on the underlying database: Invalid timestamp: '2021-02-03-22.00.00.000000'

Related to text_to_timestamp_advanced_separators is the Hyper-Q hint text_to_timestamp_advanced_separators_strict_format_checking. To learn more, see Apply Strict Checks of Teradata Timestamps.

The syntax for text_to_timestamp_advanced_separators is:

text_to_timestamp_advanced_separators = true | false

where:

Hint Scope

The text_to_timestamp_advanced_separators hint can be specified at the statement, request, session, and global level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

INSERT INTO table_name 
-- @dtm_text_to_timestamp_advanced_separators = true
VALUES ('2021-02-03-22.00.00.000000');