Schema Collections

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

Column Name	Description
COLUMN_TYPE or DATA_TYPE	Set to INTERVAL YEAR TO MONTH.
FORMAT	Format assigned to the SQL Engine object.
PROVIDERDBTYPE	It is set to TdType.IntervalYearToMonth.
INTERVAL_PRECISION	The maximum number of digits for the Year component.

Schema Table

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

Column Name	Description
NumericPrecision	The maximum number of digits for the Year component.
DataType	System.Type object for the System.String structure.
ProviderType	Set to TdType.IntervalYearToMonth.
Format	Format assigned to the SQL Engine object.
ProviderSpecificDataType	System.Type object for the TdIntervalYearToMonth structure.

Configuring Parameters

The following example shows how to configure an Interval Year-to-Month parameter using DbType type declaration and a Base Class Library (BCL) value.

Note
The Data Provider sends a VarChar data type to the SQL Engine when TdParameter.DbType is set to DbType.String. The SQL Engine will perform an implicit conversion to the Interval Year-to-Month data type when required. The "SQL Functions, Operators, Expressions and Predicates" manual documents all implicit and explicit Data Type conversions.

C#	Copy Code
TdParameter deliveryInterval = new TdParameter(); deliveryInterval.ParameterName = "deliveryYearAndMonth"; deliveryInterval.DbType = DbType.String; // 1 Year and 11 Months. deliveryInterval.Value = " 1-11";

Copy Code

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

deliveryInterval.DbType = DbType.String;

// 1 Year and 11 Months.
deliveryInterval.Value = " 1-11";

The following example shows how to configure an Interval Year-to-Month parameter using TdType type declaration and a Provider Specific value. The TdParameter.Precision property reflects the maximum number of digits for the Year 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. It is recommend to always set TdParameter.Precision property, however the Data Provider will utilize TdIntervalYearToMonth.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 Interval Value is greater than the maximum permissible value specified by the TdParameter.Precision property.

C#	Copy Code
TdParameter deliveryInterval = new TdParameter(); deliveryInterval.ParameterName = "deliveryYearAndMonth"; deliveryInterval.TdType = TdType.IntervalYearToMonth; // Maximum number of digits for the Year component. deliveryInterval.Precision = 2; // 2 Years and 11 Months. deliveryInterval.ProviderSpecificValue = new TdIntervalYearToMonth(2, 11);

Copy Code

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

deliveryInterval.TdType = TdType.IntervalYearToMonth;

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

// 2 Years and 11 Months.
deliveryInterval.ProviderSpecificValue = new TdIntervalYearToMonth(2, 11);

Specifying Interval Year To Month as Literal

The syntax for the Interval Year-to-Month Literal is INTERVALsign'yyyy-mm'YEAR TO MONTH. IntervalYear-to-Month literals consist of the word Interval followed by optional negative sign, character string literal representation of the number of Years (up to 4 digits ranging from 0 to 9999) and Months (2 digits ranging from 00 to 12) separated by hyphen and finally the phrase Year To Month.

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

The following example shows 2 Year and 4 Months represented as Interval Year-to-Month 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 2 Years and 4 Months. cmd.CommandText = "SELECT Id, orderDate from Order where ((CURRENT_DATE - OrderTimestamp) YEAR TO MONTH) > INTERVAL'2-04'YEAR TO MONTH "; 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 2 Years and 4 Months.
    cmd.CommandText = "SELECT Id, orderDate from Order where ((CURRENT_DATE - OrderTimestamp) YEAR TO MONTH) > INTERVAL'2-04'YEAR TO MONTH ";

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

Retrieving Interval Year To Month Data

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

The following methods and properties return the column or parameter value as a TdIntervalYearToMonth 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, deliveryYearsAndMonths from Order where deliveryYearsAndMonths > ?"; cmd.Parameters.Add("deliveryYearToMonths", TdType.IntervalYearToMonth); cmd.Parameters[0].Precison = 2; cmd.Parameters[0].Value = new TdIntervalYearToMonth(2, 4); using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("[String] Delivery Years and Months = {0}", reader.GetString(2)); Console.WriteLine("[TdIntervalYearToMonth] Delivery Years and Months = {0}", reader.GetTdIntervalYearToMonth(2).ToString()); } } } } /* Output: [String] Delivery Years and Months = 2-06 [TdIntervalYearToMonth] Delivery Years and Months = 2-06 */

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, deliveryYearsAndMonths from Order where deliveryYearsAndMonths > ?";
    cmd.Parameters.Add("deliveryYearToMonths", TdType.IntervalYearToMonth);
    cmd.Parameters[0].Precison = 2;
    cmd.Parameters[0].Value = new TdIntervalYearToMonth(2, 4);

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("[String] Delivery Years and Months = {0}", reader.GetString(2));
                Console.WriteLine("[TdIntervalYearToMonth] Delivery Years and Months = {0}", reader.GetTdIntervalYearToMonth(2).ToString());
            }
        }
    }
}

/* Output:
    [String] Delivery Years and Months =  2-06
    [TdIntervalYearToMonth] Delivery Years and Months =  2-06
*/

Schema Collections

Schema Table

Configuring Parameters

Specifying Interval Year To Month as Literal

Retrieving Interval Year To Month Data

See Also