Skip to main content

Datometry Documentation

Accept Timestamp Values Using Any Type of Character Separators

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'

Caution

Enabling this feature creates a larger query which may affect performance.

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:

Parameter

Description

true

Enables the use of timestamp values with any character as separators between timestamp parts.

false

Disables the use timestamp values with any character as separators between timestamp parts. This is the default behavior if not specified.

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.

Example 60. Specify that Hyper-Q use text_to_timestamp_advanced_separators as a Global Setting

You can specify text_to_timestamp_advanced_separators as a global setting in the Hyper-Q configuration file (dtm.ini). To enable text_to_timestamp_advanced_separators, set it to true. The default value is false.

"gateway".text_to_timestamp_advanced_separators = true

To learn more about specifying a hint as a gateway option in the Hyper-Q configuration file, see Gateway Configuration Parameters.



Example 61. Specify that Hyper-Q use text_to_timestamp_advanced_separators as a Query Hint

You can specify text_to_timestamp_advanced_separators as a query hint at either the session or query level. This example specifies that the hint be used at the for session level.

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

To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.