17.20 - DateTimeFormat Compatibility and Precision - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

ODBC Driver for Teradata
Release Number
June 2022
English (United States)
Last Update

If you set the session DateTimeFormat to Integer and your tables were created with Integer DATEs, TIMEs, and TIMESTAMPs, you will get the same results. The Integer time format is not recommended, because it has been deprecated. For information, see "Integer Time" in Deprecated Features for ODBC Driver for Teradata 16.20 and Later Versions.

A more interesting case is when you want to set the session DateTimeFormat to Integer, but the tables were created with ANSI DATEs, TIMEs, and TIMESTAMPs.

When the session DateTimeFormat is ANSI, the following SQL statement creates a column of type ANSI TIME with a fractional second precision of 6 (hh:mm:ss.ffffff). This is because the Teradata default fractional second precision is 6, not 0.

CREATE TABLE tablename (c1 TIME)

TIME and TIMESTAMP precision between these data types, in fractional seconds, is depicted in the following table.

Time/Timestamp Type Precision Limits
Teradata ANSI TIME up to 15 characters (hh:mm:ss.ffffff)
Teradata ANSI TIMESTAMP up to 26 characters (yyyy-mm-dd hh:mm:ss.ffffff)

If requested to fetch a character string from the database and convert it to a TIMESTAMP, ODBC Driver for Teradata will truncate the TIMESTAMP at 26 characters, and generate a warning. Similarly, when fetching a character string and converting it to a TIME, the driver will truncate the TIME at 15 characters and generate a warning. If requested to fetch a TIMESTAMP from the database and convert it to a TIME, ODBC Driver for Teradata returns SQL_SUCCESS_WITH_INFO and 01S07 as required by the ODBC specification when converting from SQL_TIMESTAMP to SQL_C_TIME. ODBC Driver for Teradata truncates the fraction silently only when fetching a character string containing a fraction field and converting it to SQL_C_TIME (converting from SQL_CHAR to SQL_C_TIME) with the DateTimeFormat DSN option for Time set to Integer. Otherwise, the truncation is not silent and ODBC Driver for Teradata returns SQL_SUCCESS_WITH_INFO and 01S07 sqlstate, as required by the ODBC specification.

If the user application calls SQLBindParameter, and passes a pointer to a buffer that contains (or will later contain) an oversize character string to be used as a TIME or TIMESTAMP, truncation of the fractional seconds can occur silently.

If the user application calls SQLBindParameter, and the session DateTimeFormat is Integer, it is not allowed to pass pointers to buffers that contain (or will later contain) Teradata ANSI DATEs, TIMEs, or TIMESTAMPs with prefixes (for example, TIME '14:25:00'). ANSI DATEs, TIMEs, and TIMESTAMPs that do not have these prefixes will work. ODBC syntax DATE, TIME, or TIMESTAMP literals will always work, regardless of the DateTimeFormat value.

You cannot do integer arithmetic on ANSI TIMEs and TIMESTAMPs. The following SQL statement fails:

SELECT ((TIME '12:13:14')(integer))/10000

When considering the tradeoffs of setting the session DateTimeFormat to Integer or to ANSI, determine if you need to do integer arithmetic on TIMEs or TIMESTAMPs.

If you use parameterized SQL against ANSI TIMEs or TIMESTAMPs, use CASTs in your SQL statements:

SELECT * FROM table WHERE columnname = CAST ((?) AS TIME(6))

Again, when deciding how to set the session DateTimeFormat, consider whether adding these CASTs to your SQL statements is feasible.

The database supports the ANSI CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP functions.

The database limits n, the fractional second precision, in a call to CURRENT_TIME(n) or CURRENT_TIMESTAMP(n), to 6. ODBC allows n to be 9. The driver passes it unaltered to the database, which will generate an error if it is larger than 6.

You can still use the ODBC scalar functions CURDATE and CURTIME, as well as the SQL statements DATE and TIME.

The EXTRACT SQL statement produces a result set that has an ODBC style title. If you prefer the title generated by the database, use SQLSetStmtOption or SQLSetStmtAttr to turn on the SQL_NOSCAN option, and do not use ODBC syntax for DATE, TIME, and TIMESTAMP literals. Alternatively, you can specify your own titles after the EXTRACT SQL statement.

You cannot use EXTRACT to obtain the default time zone from an ANSI TIME or TIMESTAMP that does not include a time zone. When using EXTRACT to obtain the time zone from an ANSI TIME or TIMESTAMP that does have a time zone, you must use the TIME or TIMESTAMP prefix:


ODBC Driver for Teradata maps both TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE to SQL_TYPE_TIME. Time zone values are truncated when converting values to SQL_C_TYPE_TIME or SQL_C_TYPE_TIMESTAMP, but are preserved when mapping to any of the ODBC C character types. Therefore, an application that requires time zone values should use the ODBC C character types.