Skip to main content

Datometry Documentation

Apply Strict Checks of Teradata Timestamps

You can perform a strict check of the Teradata default timestamp format using the Hyper-Q hint text_to_timestamp_advanced_separators_strict_format_checking.

Examples of timestamp values that will fail if you enable this feature include: '2021-02-03-22.00.000000', '2021-02-03-22.00.00.0000000', '2021g02-03g22/00/00g000000'. You must use text_to_timestamp_advanced_separators_strict_format_checking in combination with the text_to_timestamp_advanced_separators parameter.

Caution

Enabling strict timestamp checks adds significant system overhead and should only be used if an explicit failure is needed when invalid data is encountered.

Related to text_to_timestamp_advanced_separators_strict_format_checking is the Hyper-Q hint text_to_timestamp_advanced_separators. To learn more, see Accept Timestamp Values Using Any Type of Character Separators.

The syntax for text_to_timestamp_advanced_separators_strict_format_checking is:

text_to_timestamp_advanced_separators_strict_format_checking = true | false

where:

Parameter

Description

true

Enables strict timestamp checks.

false

Disables strict timestamp checks. This is the default behavior if text_to_timestamp_advanced_separators_strict_format_checking is not specified.

Hint Scope

The text_to_timestamp_advanced_separators_strict_format_checking 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 62. Specify that Hyper-Q Use Strict Timestamp Checking as a Global Setting

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

"gateway".text_to_timestamp_advanced_separators_strict_format_checking=true

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



Example 63. Specify that Hyper-Q Use Strict Timestamp Checking as a Query Hint

You can specify text_to_timestamp_advanced_separators_strict_format_checking 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_strict_format_checking=true on session
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.