DateTimeFormat Compatibility and Precision

ODBC Driver for Teradata® User Guide

brand
Teradata Tools and Utilities
prodname
ODBC Driver for Teradata
vrm_release
16.20
category
User Guide
featnum
B035-2526-108K

If you set the session DateTimeFormat to old-style and your tables were created with old-style DATEs, TIMEs, and TIMESTAMPs, you will get the same results. The integer time format is not recommended, because it has been deprecated. For more information, see Integer Time.

A more interesting case is when you want to set the session DateTimeFormat to old-style, 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 Teradata and convert it to a TIMESTAMP, the driver 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 Teradata and convert it to a TIME, the driver returns SQL_SUCCESS_WITH_INFO and 01S07 as required by the ODBC specification when converting from SQL_TIMESTAMP to SQL_C_TIME. The driver 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 the driver 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 old style, 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 trade-offs of setting the session DateTimeFormat to old style or to ANSI, you need to consider whether you need to do integer arithmetic on TIMEs or TIMESTAMPs.

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

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

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

Teradata Database supports the ANSI CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP functions.

Teradata 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 Teradata, 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 Teradata, use SQLSetStmtOption or SQLSetStmtAttr to turn on the SQL_NOSCAN option, and do not use ODBC syntax for DATE, TIME, and TIMESTAMP literals. Alternately, 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:

   SELECT EXTRACT (TIMEZONE_HOUR FROM TIME '12:13:14+07:00')

The 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.