Setting the Value of a Period Result - Advanced SQL Engine - Teradata Database

To set the value of a Period return type, follow these steps:

1. Allocate a buffer that is large enough to hold the beginning and ending bounds of the Period type, as represented by the C data type that maps to the SQL element type.

   For example, consider a CREATE FUNCTION statement that specifies a PERIOD(TIME WITH TIME ZONE) return type. To set the return value, the function must allocate a buffer that is large enough to hold two ANSI_Time_WZone values, where ANSI_Time_WZone is the C data type that maps to TIME WITH TIME ZONE.

2. Place the return value into the buffer.
3. Call FNC_SetInternalValue to set the value of the Period result to the return value in the buffer you allocated.

For detailed information on FNC_SetInternalValue, see C Library Functions.

The following code excerpt sets the value of a PERIOD(DATE) result (note that the UDF is defined so that it can pass back a null for the result):

void set_duration( DATE       *date1,
                   DATE       *date2,
                   PDT_HANDLE *result,
                   int        *date1IsNull,
                   int        *date2IsNull,
                   int        *resultIsNull,
                   char        sqlstate[6])
                   SQL_TEXT    extname[129],
                   SQL_TEXT    specific_name[129],
                   SQL_TEXT    error_message[257] )
{
    DATE new_duration[2];

    /* Set the value of the PERIOD(DATE) result. */
    if (*date2 > *date1)
    {
       new_duration[0] = *date1;
       new_duration[1] = *date2;
    }
    else if (*date1 > *date2)
    {
       new_duration[0] = *date2;
       new_duration[1] = *date1;
    }
    else
    {
       strcpy(sqlstate, "22023");
       strcpy((char *) error_message,
          "PERIOD element values cannot be equal.") ;
       *resultIsNull = -1;
       return;
    }

    FNC_SetInternalValue(*result, &new_duration[0], SIZEOF_DATE*2);

    ...
}