Schema Collections

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

Schema Table

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

Configuring Parameters

The following example shows how to configure a Time With Time Zone parameter using DbType type declaration and a BCL value. The TdParameter.DbType must be set to DbType.String because the DbType enumeration does not support Time With Time Zone. The Value property is set to a String because BCL does not have a Time With Time Zone type. The SQL Engine performs an implicit conversion from VarChar to Time With Time Zone data type when the target object (Column, Stored Procedure Parameter and etc.) is of the type Time With Time Zone.

TdParameter orderTimeWZ = new TdParameter();
orderTimeWZ.ParameterName = "orderTime";

orderTimeWZ.DbType = DbType.String;

orderTimeWZ.Value = "13:30:10.000000-07:00";

The following example shows how to configure a Time With Time Zone parameter using TdType type declaration and a Provider Specific value. The TdParameter.Scale property reflects the number of significant fractional second digits and it must be equal to or less than the target SQL Engine object (i.e. Column, Expression, Stored Procedure Parameters and etc.) declaration. It is recommend to always set TdParameter.Scale property, however the Data Provider will utilize TdTimeWithTimeZone.Scale property when the TdParameter.Scale, TdParameter.Precision and TdParametere.Size properties are all set to zero.

TdParameter orderTimeWZ = new TdParameter();
orderTimeWZ.ParameterName = "orderTime";

orderTimeWZ.TdType = TdType.TimeWithZone;

// Fractional second digits
orderTimeWZ.Scale = 6;

// Time Zone defaults to local time Zone
orderTimeWZ.ProviderSpecificValue = new TdTimeWithTimeZone(13, 30, 10);

Specifying Time as Literal

The syntax for the TIME Literal is TIME'hh:mi:ss.ssssss±hh:mi'. Time With Time Zone literals consist of the word TIME followed by character string literal representation of a Time With Time Zone value. The fractional seconds can vary from zero to six digits.

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

The following example shows 1:30:45.123 PM PDT time value represented as Time With Time Zone Literal in the Command Text.

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

    // Orders after 1:30:45.123 PM PDT
    cmd.CommandText = "SELECT Id, orderTime from Order where orderTime > TIME'13:30:45.123-07:00' ";

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

Retrieving Time With Time Zone Data

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

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 TdTimeWithTimeZone structure.

1. TdDataReader.GetTdTimeWithTimeZone
2. TdDataReader.GetProviderSpecificValue
3. TdDataReader.GetFieldValue<TdTimeWithTimeZone>
4. TdParameter.ProviderSpecificValue
5. TdParameter.GetValue<TdTimeWithTimeZone>

using (TdConnection cn = new TdConnection("data source=DS1;UserId=Joe;Password=XY;"))
{
    cn.Open();

    TdCommand cmd = cn.CreateCommand();
    cmd.CommandText = "SELECT Id, orderTimeWZ from Order where orderTimeWZ > ?";
    cmd.Parameters.Add("orderTimeWZ", TdType.TimeWithZone);
    cmd.Parameters["orderTimeWZ"].Scale = 3;
    cmd.Parameters["orderTimeWZ"].ProviderSpecificValue = new TdTimeWithTimeZone(13, 30, 45, 123000);

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("[String] OrderTimeWZ = {0}", reader.GetString(1));
                Console.WriteLine("[TdTimeWithTimeZone] OrderTimeWZ = {0}",
                                  reader.GetTdTimeWithTimeZone(1).ToString());
            }
        }
    }
}

/* Output:
    [String] OrderTimeWZ = 13:40:10.789-07:00
    [TdTimeWithTimeZone] OrderTimeWZ = 13:40:10.789-07:00
*/

See Also

Data Type Mappings

Accessor Methods for Retrieving Data

Configuring Parameters and Parameter Data Types