Period SQL to Character C type Conversion - ODBC Driver for Teradata

ODBC Driver for Teradata User Guide

Product
ODBC Driver for Teradata
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-07-11
dita:mapPath
smj1488824663219.ditamap
dita:ditavalPath
Audience_PDF_product_legacy_odbc_include.ditaval
dita:id
B035-2526
lifecycle
previous
Product Category
Teradata Tools and Utilities

The following table shows the format of returned data when retrieving it in a character format:

Returned Data Retrieved in Character Format 
Period SQL Type Conversion to SQL_C_CHAR or SQL_C_WCHAR
SQL_PERIOD_DATE The result is a character string:

(‘yyyy-mm-dd’, ‘yyyy-mm-dd’)

where the beginning and ending date bounds are converted to the ‘yyyy-mm-dd’ format that is always used by the ODBC specification when converting date SQL data (SQL_TYPE_DATE) to the character C data.

Example:

(‘2007-05-01’, ‘2007-06-01’)

SQL_PERIOD_TIME The result is a character string in the following format:

(‘hh:mi:ss[.f....]’, ‘hh:mi:ss[.f...]’)

where the beginning and ending time bounds are converted to the “hh:mi:ss” format that is always used by the ODBC specification when converting time SQL data (SQL_TYPE_TIME) to character C data.

The “[.f...]” indicates the optional fractional seconds. It is only included if the Period timestamp precision is non-zero. Up to 6 digits can be used for fractional seconds (ODBC allows for 9, but Period timestamps are limited to 6).

Example:

(‘06:20:21’, ‘17:12:00’)

SQL_PERIOD_TIME_WITH_TIME_ZONE The result is a character string in the following format:

(‘hh:mi:ss[.f...]+-hh:mi’’, ‘hh:mi:ss[.f...]+-hh:mi’’)

where the beginning and ending time bounds are converted to the “hh:mi:ss+-hh:mi’” format that is used by the ODBC driver when converting TIME WITH TIME ZONE to character C data.

Example:

(‘06:20:21.22+02:00’, ‘17:12:00.22+02:00’)

SQL_PERIOD_TIMESTAMP The result is a character string:

(‘yyyy-mm-dd hh:mi:ss[.f...]’, ‘yyyy-mm-dd hh:mi:ss[.f...]’)

where the beginning and ending timestamp bounds are converted to the “yyyy-mm-dd hh:mi:ss[.f....]” format that is always used by the ODBC specification when converting timestamp SQL data to character C data.

Example:

(‘2007-07-04 22:11:43.37’, ‘2007-07-04 22:11:50.00’)

SQL_PERIOD_TIMESTAMP_WITH_TIME_ZONE The result is the same as SQL_PERIOD_TIMESTAMP with the addition of the ‘+-hh:mi’ as described for SQL_PERIOD_TIME_WITH_TIME_ZONE.

ODBC Driver for Teradata returns SQL_ERROR from the conversion if the receiving buffer is too small. This is unlike DateTime types where a conversion to C character can result in SQL_SUCCESS_WITH_INFO and a truncated string. The reason for the difference is that a truncated DateTime string might be useful for the applications (for example: a truncated SQL_DATE might be used to obtain just the year), but a truncated Period string is not.