Schema Collections

The Schema collections return metadata for the Advanced SQL Engine objects. The notable metadata columns for the Period(Timestamp With Time Zone) data type are:

Column Name	Description
COLUMN_TYPE or DATA_TYPE	Set to PERIOD TIMESTAMP WITH TIME ZONE.
DATETIME_PRECISION	The number of digits in the fractional seconds component of the Period(Time With Time Zone) data type. The range of values are from Zero to Six.
FORMAT	Format assigned to the SQL Engine object.
PROVIDERDBTYPE	Set to TdType.PeriodTimestampWithTimeZone.

Schema Table

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

Column Name	Description
ColumnSize	The maximum length in Characters.
NumericScale	The number of digits in the fractional seconds component of the Period(Time With Time Zone) data type. The range of values are from Zero to Six.
DataType	System.Type object for the System.String structure.
ProviderType	Set to TdType.PeriodTimestampWithTimeZone.
Format	Format assigned to the SQL Engine object.
ProviderSpecificDataType	System.Type object for the TdPeriodTimestampWithTimeZone structure.

Configuring Parameters

The following example shows how to configure a Period(Timestamp 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 SQL Engine when TdParameter.DbType is set to DbType.String. The SQL Engine will perform an implicit conversion to the Period(Timestamp With Time Zone) data type when required. The "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-01-01 00:00:00.000-11:00, 2011-01-31 23:59:59.999-11:00)";

The following example shows how to configure a Period(Timestamp 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 SQL Engine 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 TdPeriodTimestampWithTimeZone.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.

Note
TdTimestampWithTimeZone.MaxValue is equivalent to the SQL Engine reserved word UNTIL_CHANGED

C#	Copy Code
TdParameter deliveryPeriod = new TdParameter(); deliveryPeriod.TdType = TdType.PeriodTimestampWithTimeZone; // Maximum number of digits for the Fractional Second component. deliveryPeriod.Scale = 6; // The Time Zone defaults to local Time Zone. deliveryPeriod.ProviderSpecificValue = new TdPeriodTimestampWithTimeZone( new TdTimestampWithTimeZone(2011, 1, 1, 0, 0, 0), new TdTimestampWithTimeZone(2011, 1, 31, 23, 59, 59, 999999));

Copy Code

TdParameter deliveryPeriod = new TdParameter();

deliveryPeriod.TdType = TdType.PeriodTimestampWithTimeZone;

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

// The Time Zone defaults to local Time Zone.
deliveryPeriod.ProviderSpecificValue = new TdPeriodTimestampWithTimeZone(
    new TdTimestampWithTimeZone(2011, 1, 1, 0, 0, 0),
    new TdTimestampWithTimeZone(2011, 1, 31, 23, 59, 59, 999999));

Specifying Period(Timestamp With Time Zone) as Literal

The syntax for the Period(Timestamp With Time Zone) Literal is PERIOD'(YYY-MM-DD hh:mi:ss.ffffffSIGNhh:mi, YYYY-MM-DD hh:mi:ss.ffffffSIGNhh:mi)'. Period(Timestamp 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 SQL Engine's Request Cache.

The following example shows a Period(Timestamp With Time Zone) Literal embedded 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(); cmd.CommandText = "INSERT INTO Policy (PolicyId, Validity) " + "VALUES (2001, PERIOD'(2011-01-01 08:00:00.000-08:00, 2011-01-31 17:00:00.000-08:00)')"; 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 (2001, PERIOD'(2011-01-01  08:00:00.000-08:00, 2011-01-31 17:00:00.000-08:00)')";

    cmd.ExecuteNonQuery();
}

Retrieving Period(Timestamp With Time Zone) 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 TdPeriodTimestampWithTimeZone 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.PeriodTimestampWithTimeZone); cmd.Parameters[0].Scale = 3; // Time Zone defaults to Local Time Zone. cmd.Parameters[0].Value = new TdPeriodTimestampWithTimeZone( new TdTimestampWithTimeZone(2011, 1, 1, 8, 0, 0), new TdTimestampWithTimeZone(2011, 1, 31, 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("[TdPeriodTimestampWithTimeZone] Policy Validity = {0}", reader.GetTdPeriodTimestampTimeWithTimeZone(1).ToString()); } } } } /* Output: [String] Policy Validity = (2011-02-01 09:30:00.000-08:00, 2011-02-15 16:30:00.000-08:00) [TdPeriodTimestampWithTimeZone] Policy Validity = (2011-02-01 09:30:00.000-08:00, 2011-02-15 16:30:00.000-08:00) */

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

    cmd.Parameters[0].Scale = 3;

    // Time Zone defaults to Local Time Zone.
    cmd.Parameters[0].Value = new TdPeriodTimestampWithTimeZone(
        new TdTimestampWithTimeZone(2011, 1, 1, 8, 0, 0),
        new TdTimestampWithTimeZone(2011, 1, 31, 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("[TdPeriodTimestampWithTimeZone] Policy Validity = {0}",
                    reader.GetTdPeriodTimestampTimeWithTimeZone(1).ToString());
            }
        }
    }
}

/* Output:
    [String] Policy Validity =  (2011-02-01 09:30:00.000-08:00, 2011-02-15 16:30:00.000-08:00)
    [TdPeriodTimestampWithTimeZone] Policy Validity =  (2011-02-01 09:30:00.000-08:00, 2011-02-15 16:30:00.000-08:00) 
*/

Schema Collections

Schema Table

Configuring Parameters

Specifying Period(Timestamp With Time Zone) as Literal

Retrieving Period(Timestamp With Time Zone) Data

See Also