Schema Collections

The Schema collections return metadata for the Advanced SQL Engine objects. The notable metadata columns for the Interval Hour-to-Minute data type are:

Column Name	Description
COLUMN_TYPE or DATA_TYPE	Set to INTERVAL HOUR TO MINUTE.
FORMAT	Format assigned to the SQL Engine object.
PROVIDERDBTYPE	It is set to TdType.IntervalHourToMinute.
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-Minute data type are:

Column Name	Description
NumericPrecision	The maximum number of digits for the Hour component.
DataType	System.Type object for the System.TimeSpan structure.
ProviderType	Set to TdType.IntervalHourToMinute.
Format	Format assigned to the SQL Engine object.
ProviderSpecificDataType	System.Type object for the TdIntervalHourToMinute structure.

Configuring Parameters

The following example shows how to configure an Interval Hour-to-Minute 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 SQL Engine object (i.e. Column, Expression, Stored Procedure Parameters and etc.) declaration. The range of values are from One to Four.

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 property of the System.TimeSpan value is greater than the maximum permissible value specified by TdParameter.Precision property.

Note
The Data Provider truncates the Seconds and Milliseconds components of the System.TimeSpan vlaue.

C#	Copy Code
TdParameter deliveryInterval = new TdParameter(); deliveryInterval.ParameterName = "deliveryHoursAndMinutes"; deliveryInterval.DbType = DbType.Object; // Maximum number of digits for the Hour component deliveryInterval.Precision = 2; // 12 Hours and 35 Minutes. deliveryInterval.Value = new System.TimeSpan(0, 12, 35, 0);

Copy Code

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

deliveryInterval.DbType = DbType.Object;

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

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

The following example shows how to configure an Interval Hour-to-Minute parameter using TdType type declaration and a Provider Specific value. It is recommend to always set TdParameter.Precision property, however the Data Provider will utilize TdIntervalHourToMinute.Precision property value when the TdParameter.Precision property is 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 Hour Component is greater than the maximum permissible value specified by the TdParameter.Precision property.

C#	Copy Code
TdParameter deliveryInterval = new TdParameter(); deliveryInterval.ParameterName = "deliveryHoursAndMinutes"; deliveryInterval.TdType = TdType.IntervalHourToMinute; // Maximum number of digits for the Hour component. deliveryInterval.Precision = 2; // 12 Hours and 35 Minutes. deliveryInterval.ProviderSpecificValue = new TdIntervalHourToMinute(12, 35);

Copy Code

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

deliveryInterval.TdType = TdType.IntervalHourToMinute;

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

// 12 Hours and 35 Minutes.
deliveryInterval.ProviderSpecificValue = new TdIntervalHourToMinute(12, 35);

Specifying Interval Hour To Minute as Literal

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

Note
We recommend to always use Parameters in order to take advantage of the SQL Engine's Request Cache.

The following example shows 3 hours and 59 Minutes represented as Interval Hour-to-Minute 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 3 hours and 59 minutes. cmd.CommandText = "SELECT Id, orderTS from Order where (CURRENT_TIMESTAMP - OrderTS) > INTERVAL'3:59'HOUR TO MINUTE "; 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 3 hours and 59 minutes.
    cmd.CommandText = "SELECT Id, orderTS from Order where (CURRENT_TIMESTAMP - OrderTS) > INTERVAL'3:59'HOUR TO MINUTE ";

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

Retrieving Interval Hour To Minute 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 TdIntervalHourToMinute 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, deliveryHoursAndMinutes from Order where deliveryHoursAndMinutes > ?"; cmd.Parameters.Add("delivery", TdType.IntervalHourToMinute); cmd.Parameters[0].Precison = 2; cmd.Parameters[0].Value = new TimeSpan(0, 3, 59, 0); using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("[TimeSpan] Delivery Hour to Minute = {0}", reader.GetValue(2).ToString()); Console.WriteLine("[TdIntervalHourToMinute] Delivery Hour to Minute = {0}", reader.GetTdIntervalHourToMinutes(2).ToString()); } } } } /* Output: [TimeSpan] Delivery Hour to Minute = 03:09:00.0000000 [TdIntervalHourToMinute] Delivery Hour to Minute = 3:09 */

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, deliveryHoursAndMinutes from Order where deliveryHoursAndMinutes > ?";
    cmd.Parameters.Add("delivery", TdType.IntervalHourToMinute);
    cmd.Parameters[0].Precison = 2;
    cmd.Parameters[0].Value = new TimeSpan(0, 3, 59, 0);

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

/* Output:
    [TimeSpan] Delivery Hour to Minute = 03:09:00.0000000
    [TdIntervalHourToMinute] Delivery Hour to Minute = 3:09
*/

Schema Collections

Schema Table

Configuring Parameters

Specifying Interval Hour To Minute as Literal

Retrieving Interval Hour To Minute Data

See Also