15.10 - Retrieving Period Data - ODBC Driver for Teradata

ODBC Driver for Teradata User Guide

prodname
ODBC Driver for Teradata
vrm_release
15.10
category
User Guide
featnum
B035-2509-035K

Period data received from the database can be retrieved in a character format (SQL_C_CHAR and SQL_C_WCHAR) or as binary data (SQL_C_BINARY).

The default conversion as specified by SQL_C_DEFAULT is the same as the conversion specified by SQL_C_BINARY.

Period SQL to Character C type Conversion

Table 44 shows the format of returned data when retrieving it in a character format:

 

Table 44: 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.

Period SQL to Binary C type Conversion

Table 45 shows the format of returned data when retrieving it in a binary format.

 

Table 45: Returned Data Retrieved in Binary Format 

Period Data Type

Number of Bytes

Members Passed

Members Detail (in the order specified)

PERIOD(DATE)

8

2 DATE members

Date: 4-byte, signed integer. This integer represents a date in the same manner as for a DATE data type (for example, 10000*(year-1900)) + (100*month) + day)

PERIOD(TIME(n))

12

2 TIME members

Second: 4-byte, signed integer. This integer represents the number of seconds scaled by 10**6 (for example, 12.56 seconds is returned as 12560000).

Hour: 1 unsigned byte. This byte represents the number of hours.

Minute: 1 unsigned byte to client form. This byte represents the number of minutes.

PERIOD(TIME(n) WITH TIME ZONE)

16

2 TIME WITH TIME ZONE members

Second: 4-byte, signed integer. This integer represents the number of seconds scaled by 10**6 (for example, 12.56 seconds is returned as 12560000).

Hour: 1 unsigned byte. This byte represents the number of hours.

Minute: 1 unsigned byte. This byte represents the number of minutes.

Time Zone Hour: 1 unsigned byte. This byte represents the hours portion of the time zone displacement, along with whether the displacement is positive or negative. A value of 16 represents 0 hours. A value less than 16 represents a negative time zone displacement for the specified hours; for example, if this is 10, the time zone is displaced by -10 hours. If the value is greater than 16, it specifies a positive time zone displacement of (Time Zone Hour - 16) hours; that is, a value of 20 implies a +4 hour displacement.)

Time Zone Minute: 1 unsigned byte. This byte represents the minutes portion of the time zone displacement.

PERIOD(TIMESTAMP(n))

20

2 TIMESTAMP members

Two TIMESTAMP members containing:

Second: 4-byte, signed integer. This integer represents the number of seconds scaled by 10**6 (for example, 12.56 seconds is returned as 12560000).

Year: 2-byte, signed short integer. This byte represents the year value.

Month: 1 unsigned byte. This byte represents the month value.

Day: 1 unsigned byte. This byte represents the day of the month.

Hour: 1 unsigned byte. This byte represents the number of hours.

Minute: 1 unsigned byte. This byte represents the number of minutes.

PERIOD(TIMESTAMP(n) WITH TIME ZONE)

24

2 TIMESTAMP WITH TIME ZONE members

Two TIMESTAMP members containing:

Second: 4-byte, signed integer. This integer represents the number of seconds scaled by 10**6 (for example, 12.56 seconds is returned as 12560000).

Year: 2-byte, signed short integer. This byte represents the year value.

Month: 1 unsigned byte. This byte represents the month value.

Day: 1 unsigned byte. This byte represents the day of the month.

Hour: 1 unsigned byte. This byte represents the number of hours.

Minute: 1 unsigned byte. This byte represents the number of minutes.

Time Zone Hour: 1 unsigned byte. This byte represents the time zone displacement in hours, along with whether the displacement is positive or negative. A value of 16 represents 0 hours. A value less than 16 represents a negative time zone displacement for the specified hours; for example, if this value is 10, the time zone is displaced by -10 hours. If the value is greater than 16, it specifies a positive time zone displacement of (Time Zone Hour - 16) hours; that is, a value of 20 implies a +4 hour displacement.)

Time Zone Minute: 1 unsigned byte. This byte represents the minutes portion of the time zone displacement.

Any target precision set by the application in the application record descriptor is ignored by the driver. The Second values returned by the database are always 4-byte signed integers with the seconds scaled by 10**6. The precision of the source data is available through SQLDescribeCol/SQLColAttribute or directly in the Implementation Record Descriptor.