Schema Collections

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

Schema Table

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

Configuring Parameters

The following example shows how to configure a Period(Time With Time Zone) 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(Time With Time Zone) data type when required. The Teradata Database SQL Functions, Operators, Expressions and Predicates manual documents all implicit and explicit Data Type conversions.

TdParameter deliveryPeriod = new TdParameter();

deliveryPeriod.DbType = DbType.String;

deliveryPeriod.Value = "(02:54:56.49-11:00, 12:31:47.84-11:00)";

The following example shows how to configure a Period(Time With Time Zone) parameter using TdType type declaration and a Provider Specific value. 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. The range of values are from Zero to Six. It is recommend to always set TdParameter.Scale property, however the Data Provider will utilize TdPeriodTimeWithTimeZone.Scale property value when the TdParameter.Scale, TdParameter.Precision and TdParameter.Size  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.

TdParameter deliveryPeriod = new TdParameter();

deliveryPeriod.TdType = TdType.PeriodTimeWithTimeZone;

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

// The Time Zone defaults to local Time Zone.
deliveryPeriod.ProviderSpecificValue = new TdPeriodTimeWithTimeZone(new TdTimeWithTimeZone(10, 15, 25, 123), new TdTimeWithTimeZone(15, 0, 0, 0));

Specifying Period(Time With Time Zone) as Literal

The syntax for the Period(Time With Time Zone) Literal is PERIOD'(hh:mi:ss.ffffffSIGNhh:mi, hh:mi:ss.ffffffSIGNhh:mi)'. Period(Time With Time Zone) literals consist of the word PERIOD followed by character string literal representation of a beginning and ending bounds. The fractional seconds can vary from zero to six characters.

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

The following example shows a Period(Time With Time Zone) Literal embedded in the command Text.

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 (2001, PERIOD'(08:00:00.000-08:00, 17:00:00.000-08:00)')";

    cmd.ExecuteNonQuery();
}

Retrieving Period(Time With Time Zone) Data

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

1. TdDataReader.GetString
2. TdDataReader.GetValue
3. TdDataReader.GetFieldValue<String>
4. TdParameter.Value
5. TdParameter.GetValue<String>

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

1. TdDataReader.GetTdPeriodTimeWithTimeZone
2. TdDataReader.GetProviderSpecificValue
3. TdDataReader.GetFieldValue<TdPeriodTimeWithTimeZone>
4. TdParameter.ProviderSpecificValue
5. TdParameter.GetValue<TdPeriodTimeWithTimeZone>

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.PeriodTimeWithTimeZone);

    cmd.Parameters[0].Scale = 6;

    // Time Zone defaults to Local Time Zone.
    cmd.Parameters[0].Value = new TdPeriodTimeWithTimeZone(new TdTimeWithTimeZone(8, 0, 0), new TdTimeWithTimeZone(17, 0, 0) );

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("[String] Policy Validity = {0}", reader.GetString(1));
                Console.WriteLine("[TdPeriodTimeWithTimeZone] Policy Validity = {0}", reader.GetTdPeriodTimeWithTimeZone(1).ToString());
            }
        }
    }
}

/* Output:
    [String] Policy Validity =  (09:30:00.000-08:00, 16:30:00.000-08:00)
    [TdPeriodTimeWithTimeZone] Policy Validity =  (09:30:00.000-08:00:, 16:30:00.000-08:00) 
*/

See Also

Data Type Mappings

Accessor Methods for Retrieving Data

Configuring Parameters and Parameter Data Types