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:
|
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_ |
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.
|
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.