Schema Collections

The Schema collections return metadata for the Teradata Database objects. The notable metadata columns for the Interval Hour-to-Second data type are:

Column Name	Description
COLUMN_TYPE or DATA_TYPE	Set to INTERVAL HOUR TO SECOND.
FORMAT	Teradata Database Format assigned to the Teradata Database object.
PROVIDERDBTYPE	It is set to TdType.IntervalHourToSecond.
DATETIME_PRECISION	The maximum number of digits for the fractional-second component.
INTERVAL_PRECISION	The maximum number of digits for the Hour component.

Schema Table

The TdDataReader.SchemaTable returns result set metadata. The notable metadata columns for the Interval Hour-to-Second data type are:

Column Name	Description
NumericPrecision	The maximum number of digits for the Hour component.
NumericScale	The maximum number of digits for the fractional-second component.
DataType	System.Type object for the System.TimeSpan structure.
ProviderType	Set to TdType.IntervalHourToSecond.
Format	Teradata Database Format assigned to the Teradata Database object.
ProviderSpecificDataType	System.Type object for the TdIntervalHourToSecond structure.

Configuring Parameters

The following example shows how to configure an Interval Hour-to-Second parameter using DbType type declaration and a Base Class Library (BCL) value. The TdParameter.Precision property reflects the maximum number of digits for the Hour component and it must be equal to or less than the target Teradata Database object (i.e. Column, Expression, Stored Procedure Parameters and etc.) declaration. The range of values are from One to Four. The TdParameter.Scale property reflects the maximum number of digits for the fractional-second component and it must be equal to or less than the target Teradata Database object (i.e. Column, Expression, Stored Procedure Parameters and etc.) declaration.

Note
The Data Provider will throw an exception if the TotalHours property of the System.TimeSpan value is greater than 9,999 Hours. The Data Provider will throw an exception if the TotalHours proeprty of the System.TimeSpan value is greater than the maximum permissible value specified by TdParameter.Precision property.

Note
The Data Provider truncates the fractional seconds to the Scale specified.

C#	Copy Code
TdParameter deliveryInterval = new TdParameter(); deliveryInterval.ParameterName = "deliveryHoursAndMinute"; deliveryInterval.DbType = DbType.Object; // Maximum number of digits for the Hour component deliveryInterval.Precision = 2; // Maximum number of Fractional-Second digits deliveryInterval.Scale = 3; // 12 Hours, 35 Minutes, 3 seconds and 10 milliseconds. deliveryInterval.Value = new System.TimeSpan(0, 12, 35, 3, 10);

Copy Code

TdParameter deliveryInterval = new TdParameter();
deliveryInterval.ParameterName = "deliveryHoursAndMinute";

deliveryInterval.DbType = DbType.Object;

// Maximum number of digits for the Hour component
deliveryInterval.Precision = 2;

// Maximum number of Fractional-Second digits
deliveryInterval.Scale = 3;

// 12 Hours, 35 Minutes, 3 seconds and 10 milliseconds.
deliveryInterval.Value = new System.TimeSpan(0, 12, 35, 3, 10);

The following example shows how to configure an Interval Hour-to-Second parameter using TdType type declaration and a Provider Specific value. It is recommend to always set TdParameter.Precision and TdParameter.Scale properties, however the Data Provider will utilize TdIntervalHourToSecond.Precision and TdIntervalHourToSecond.Scale property values when the TdParameter.Precision and TdParameter.Scale properties are set to zero. .NET Applications can retrieve metadata from the Schema Collections or the Schema Table and apply the metadata to the TdParameter object.

Note
The Data Provider will throw an exception if the Interval Hour component is greater than the maximum permissible value specified by the TdParameter.Precision property.

Note
The Data Provider truncates the fractional seconds to the TdParameter.Scale specified.

C#	Copy Code
TdParameter deliveryInterval = new TdParameter(); deliveryInterval.ParameterName = "deliveryHourToSecond"; deliveryInterval.TdType = TdType.IntervalHourToSecond; // Maximum number of digits for the Hour component deliveryInterval.Precision = 2; // Maximum number of digits for the Fractional-Second component deliveryInterval.Scale = 6; // 12 Hours, 35 Minutes, 3 seconds and 10 microseconds . deliveryInterval.ProviderSpecificValue = new TdIntervalHourToSecond(0, 12, 35, 3, 10);

Copy Code

TdParameter deliveryInterval = new TdParameter();
deliveryInterval.ParameterName = "deliveryHourToSecond";

deliveryInterval.TdType = TdType.IntervalHourToSecond;

// Maximum number of digits for the Hour component
deliveryInterval.Precision = 2;

// Maximum number of digits for the Fractional-Second component
deliveryInterval.Scale = 6;

// 12 Hours, 35 Minutes, 3 seconds and 10 microseconds  .
deliveryInterval.ProviderSpecificValue = new TdIntervalHourToSecond(0, 12, 35, 3, 10);

Specifying Interval Hour To Second as Literal

The syntax for the Interval Hour-to-Second Literal is INTERVALsign'hhhh:mi:ss.ffffff'HOUR TO SECOND. Interval Hour-to-Second literals consist of the word Interval followed by an optional negative sign, character string literal representation of the number of Hours (up to 4 digits ranging from 0 to 9999), Minutes (2 digits ranging from 00 to 59), Seconds (2 digits ranging from 00 to 59) and Fractional-Seconds (up to 6 digits ranging from 0 to 999999) and the phrase Hour To Second.

Note
We recommend to always use Parameters in order to take advantage of the Teradata Database Request Cache.

The following example shows 789 hours, 59 Minutes, 3 seconds and 10 milliseconds represented as Interval Hour-to-Second Literal in the Command Text.

C#	Copy Code
using (TdConnection cn = new TdConnection("data source=x;UserId=y;Password=z;")) { cn.Open(); TdCommand cmd = cn.CreateCommand(); // Delivery exceeding 789 hours, 59 minutes, 3 seconds and 10 milliseconds. cmd.CommandText = "SELECT Id, orderDate from Order where (CURRENT_TIMESTAMP - OrderTS) > INTERVAL'789:59:03.010'HOUR TO SECOND "; using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("Id={0}", reader.GetInt64(0).ToString()); } } } }

Copy Code

using (TdConnection cn = new TdConnection("data source=x;UserId=y;Password=z;"))
{
    cn.Open();
    TdCommand cmd = cn.CreateCommand();

    // Delivery exceeding 789 hours, 59 minutes, 3 seconds and 10 milliseconds.
    cmd.CommandText = "SELECT Id, orderDate from Order where (CURRENT_TIMESTAMP - OrderTS) > INTERVAL'789:59:03.010'HOUR TO SECOND ";

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("Id={0}", reader.GetInt64(0).ToString());
            }
        }
    }
}

Retrieving Interval Hour To Second Data

The following methods and properties return the column or parameter value as a System.TimeSpan structure.

The following methods and properties return the column or parameter value as a TdIntervalHourToSecond structure.

C#	Copy Code
using (TdConnection cn = new TdConnection("data source=DS1;UserId=Joe;Password=XY;")) { cn.Open(); TdCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT Id, orderDate, deliveryHoursAndSeconds from Order where deliveryHoursAndSeconds > ?"; cmd.Parameters.Add("delivery", TdType.IntervalHourToSecond); cmd.Parameters[0].Precison = 2; cmd.Parameters[0].Scale = 3; cmd.Parameters[0].Value = new TimeSpan(0, 3, 59, 3, 10); using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("[TimeSpan] Delivery Hour to Second = {0}", reader.GetValue(2).ToString()); Console.WriteLine("[TdIntervalHourToSecond] Delivery Hour to Second = {0}", reader.GetTdIntervalHourToSecond(2).ToString()); } } } } /* Output: [TimeSpan] Delivery Hour to Seconds = 03:09:00.0000000 [TdIntervalHourToSecond] Delivery Hour to Second = 3:09:00.000 */

Copy Code

using (TdConnection cn = new TdConnection("data source=DS1;UserId=Joe;Password=XY;"))
{
    cn.Open();

    TdCommand cmd = cn.CreateCommand();
    cmd.CommandText = "SELECT Id, orderDate, deliveryHoursAndSeconds from Order where deliveryHoursAndSeconds > ?";
    cmd.Parameters.Add("delivery", TdType.IntervalHourToSecond);
    cmd.Parameters[0].Precison = 2;
    cmd.Parameters[0].Scale = 3;
    cmd.Parameters[0].Value = new TimeSpan(0, 3, 59, 3, 10);

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("[TimeSpan] Delivery Hour to Second = {0}", reader.GetValue(2).ToString());
                Console.WriteLine("[TdIntervalHourToSecond] Delivery Hour to Second = {0}", reader.GetTdIntervalHourToSecond(2).ToString());
            }
        }
    }
}

/* Output:
    [TimeSpan] Delivery Hour to Seconds = 03:09:00.0000000
    [TdIntervalHourToSecond] Delivery Hour to Second = 3:09:00.000
*/

Schema Collections

Schema Table

Configuring Parameters

Specifying Interval Hour To Second as Literal

Retrieving Interval Hour To Second Data

See Also