For an example using descriptors, see Period Datatype Example Using Descriptor.
For COBOL examples, see Period Datatype Examples.
Example 1
This is an example of a C program that uses PERIOD(TIME WITH TIMEZONE) and demonstrates how each field is returned. The period data is retrieved from the database in binary format.
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
char Time[16];
short TimeInd;
char LOGON_STR[70];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
struct time
{
SQLInt32 tSeconds;
unsigned char tHour;
unsigned char tMinute;
unsigned char tTZHour;
unsigned char tTZMinute;
};
/**********************************************************************/
/* format time */
/**********************************************************************/
void formatTime(out, in, len)
char* in, *out;
int len;
{
int sec1, hsec1, tzhour;
struct time time1;
char sign;
memcpy(&time1, in, len);
sec1 = time1.tSeconds / 1000000;
hsec1 = time1.tSeconds % 1000000;
if (len == 6) /* timezone not included */
{
sprintf(out, "%02d:%02d:%02d.%04d",
time1.tHour, time1.tMinute,sec1,hsec1);
}
else /* timezone is included */
{
/* can't use both + and 0 sprintf flags, do sign manually */
if (time1.tTZHour >= 16)
{
sign = '+';
time1.tTZHour -= 16;
}
else
sign = '-';
sprintf(out, "%02d:%02d:%02d.%04d%c%02d:%02d",
time1.tHour, time1.tMinute,sec1,hsec1, sign, time1.tTZHour,time1.tTZMinute );
}
}
/**********************************************************************/
/* main */
/**********************************************************************/
main ()
{
/* assuming a table created similar to
CREATE TABLE tab01
(
...
PeriodTZ PERIOD(TIME WITH TIME ZONE)
...
) ;
*/
strcpy(LOGON_STR,"mydbs/myuser,mypw");
EXEC SQL
LOGON :LOGON_STR;
EXEC SQL DECLARE CURa CURSOR FOR
SELECT PeriodTZ from tab01;
EXEC SQL OPEN CURa;
while (SQLCODE == 0)
{
EXEC SQL FETCH CURa
INTO :Time :TimeInd;
if (SQLCODE == 0)
{
char t1[8], t2[8];
if (TimeInd == -1)
printf("\nTime is Null");
else
{
formatTime(t1, Time, 8);
printf("\nTime 1 = %s", t1);
formatTime(t2, Time+8, 8);
printf("\nTime 2 = %s", t2);
}
}
}
EXEC SQL
LOGOFF;
}
Example 2
This example’s use of Teradata casting operators is an easier way to retrieve character formatting and retrieve period data from the database than shown in Example 1.
CREATE TABLE tab02
(
MyKey int,
PeriodD PERIOD(DATE),
PeriodT PERIOD(TIME)
) ;
INSERT INTO tab02 VALUES (1,
PERIOD(CAST('2005-02-13' AS DATE), CAST('2006-02-13' AS DATE)),
PERIOD(TIME '12:00:00', TIME '13:59:59'));
SELECT PeriodT (CHARACTER(38)) FROM tab02;
Returns: ('12:00:00.000000', '13:59:59.000000')
Example 3
This example uses casting operators to convert character datatypes to period datatypes when inserting data into the database.
EXEC SQL CREATE TABLE tab01 ( Key INTEGER, PeriodD PERIOD(DATE), PeriodT PERIOD(TIME), PeriodTZ PERIOD(TIME WITH TIME ZONE), PeriodTS PERIOD(TIMESTAMP), PeriodTSZ PERIOD(TIMESTAMP WITH TIME ZONE) ); char TIME1[9], TIME2[9]; short TIMEIND; char TIMESTAMPZ1 [29], TIMESTAMPZ2 [29]; strcpy(TIME1,”12:00:00”); strcpy(TIME2,”13:00:00”); strcpy(TIMESTAMPZ1,”2013-01-02 12:00:00.33-01:00”); strcpy(TIMESTAMPZ2,” 2013-02-03 13:59:59.44-01:00”); EXEC SQL INSERT INTO tab01 VALUES (1, PERIOD(DATE '1900-07-07', DATE '1900-07-27'), PERIOD(TIME :TIME1, TIME :TIME2) :TIMEIND, PERIOD(TIME '01:02:03.400+01:00', TIME '02:48:48.400+01:00'), PERIOD(TIMESTAMP '2013-06-27 01:02:03.400', TIMESTAMP '2013-07-04 13:14:15.16'), PERIOD(TIMESTAMP :TIMESTAMPZ1, TIMESTAMP : TIMESTAMPZ2) );