Schema Collections

The Schema collections return metadata for the Teradata Database objects. The notable metadata columns for the Period(Date) data type are:

Column Name	Description
COLUMN_TYPE or DATA_TYPE	Set to PERIOD DATE.
FORMAT	Teradata Database Format assigned to the Teradata Database object.
PROVIDERDBTYPE	Set to TdType.PeriodDate.

Column Name

Description

COLUMN_TYPE

DATA_TYPE

Set to PERIOD DATE.

FORMAT

Teradata Database Format assigned to the Teradata Database object.

PROVIDERDBTYPE

Set to TdType.PeriodDate.

Schema Table

The TdDataReader.SchemaTable returns result set metadata. The notable metadata columns for the Period(Date) data type are:

Column Name	Description
ColumnSize	The maximum length in Characters.
DataType	System.Type object for the System.String structure.
ProviderType	Set to TdType.PeriodDate.
Format	Teradata Database Format assigned to the Teradata Database object.
ProviderSpecificDataType	System.Type object for the TdPeriodDate structure.

Configuring Parameters

The following example shows how to configure a Period(Date) parameter using DbType type declaration and a Base Class Library (BCL) value.

Note
The Data Provider sends a VarChar data type to the Teradata Database when TdParameter.DbType is set to DbType.String. The Teradata Database will perform an implicit conversion to the Period(Date) data type when required. The Teradata Database SQL Functions, Operators, Expressions and Predicates manual documents all implicit and explicit Data Type conversions.

C#	Copy Code
TdParameter deliveryPeriod = new TdParameter(); deliveryPeriod.DbType = DbType.String; deliveryPeriod.Value = "(2011-02-01, 2011-02-15)";

The following example shows how to configure a Period(Date) parameter using TdType type declaration and a Provider Specific value. .NET Applications can retrieve metadata from the Schema Collections or the Schema Table and apply the metadata to the TdParameter object.

Note
TdDate.MaxValue is equivalent to the Teradata Database reserved work UNTIL_CHANGED

C#	Copy Code
TdParameter deliveryPeriod = new TdParameter(); deliveryPeriod.TdType = TdType.PeriodDate; deliveryPeriod.ProviderSpecificValue = new TdPeriodDate(new TdDate(2011, 2, 1), TdDate.MaxValue);

Specifying Period(Date) as Literal

The syntax for the Period(Date) Literal is one of the following two forms:

PERIOD'(YYYY-MM-DD, YYYY-MM-DD)'
PERIOD'(YYYY-MM-DD, UNTIL_CHANGED)'

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

The following example shows all policies enacted starting from January 1, 2011.

C#	Copy Code
using (TdConnection cn = new TdConnection("data source=x;UserId=y;Password=z;")) { cn.Open(); TdCommand cmd = cn.CreateCommand(); cmd.CommandText = "INSERT INTO Policy (PolicyId, Validity) VALUES (1001, PERIOD'(2011-01-01, UNTIL_CHANGED)')"; cmd.ExecuteNonQuery(); }

Copy Code

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

    cmd.CommandText = "INSERT INTO Policy (PolicyId, Validity) VALUES (1001, PERIOD'(2011-01-01, UNTIL_CHANGED)')";

    cmd.ExecuteNonQuery();
}

Retrieving Period(Date) 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 TdPeriodDate 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 PolicyId, Validity from Policy where Validity > ?"; cmd.Parameters.Add("Validity", TdType.PeriodDate); cmd.Parameters[0].Value = new TdPeriodDate(new TdDate(2011, 2, 1), TdDate.MaxValue ); using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("[String] Policy Validity = {0}", reader.GetString(1)); Console.WriteLine("[TdPeriodDate] Policy Validity = {0}", reader.GetTdPeriodDate(1).ToString()); } } } } /* Output: [String] Policy Validity = (2011-03-01, 9999-12-31) [TdPeriodDate] Policy Validity = (2011-03-01, 9999-12-31) */