Schema Collections

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

Column Name	Description
COLUMN_TYPE or DATA_TYPE	Set to TIME.
FORMAT	Format assigned to the SQL Engine object.
DATETIME_PRECISION	The number of significant digits in the fractional seconds portion of the Time. The range of values are from Zero to Six.
PROVIDERDBTYPE	It is set to TdType.Time.

Schema Table

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

Column Name	Description
NumericScale	The number of significant digits in the fractional seconds portion of the Time. The range of values are from Zero to Six.
DataType	System.Type object for the System.TimeSpan structure.
ProviderType	Set to TdType.Time.
Format	Format assigned to the SQL Engine object.
ProviderSpecificDataType	System.Type object for the TdTime structure.

Configuring Parameters

The following example shows how to configure a Time parameter using DbType type declaration and a Base Class Library (BCL) value. The TdParameter.Scale property reflects the number of fractional seconds 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. The range of values are from Zero to Six.

Note
The Data Provider will throw an exception if the Day component of the TimeSpan value is not Zero. The Data Provider will throw an exception if the TimeSpan is negative (less than TimeSpan.Zero).

Note
The Data Provider truncates the fractional seconds to the Scale specified.

C#	Copy Code
TdParameter orderTime = new TdParameter(); orderTime.ParameterName = "orderTime"; orderTime.DbType = DbType.Time; // Fractional Second Digits orderTime.Scale = 6; orderTime.Value = new System.TimeSpan(13, 30, 10);

Copy Code

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

orderTime.DbType = DbType.Time;

// Fractional Second Digits
orderTime.Scale = 6;

orderTime.Value = new System.TimeSpan(13, 30, 10);

The following example shows how to configure a Time parameter using TdType type declaration and a Provider Specific value. It is recommend to always set TdParameter.Scale property, however the Data Provider will utilize TdTime.Scale property value when the TdParameter.Scale, TdParameter.Precision and TdParameter.Size properties are all set to zero. .NET Applications can retrieve metadata from the Schema Collections or the SchemaTable and apply the metadata to the TdParameter object.

C#	Copy Code
TdParameter orderTime = new TdParameter(); orderTime.ParameterName = "orderTime"; orderTime.TdType = TdType.Time; // Fractional Second Digits orderTime.Scale = 6; orderTime.ProviderSpecificValue = new TdTime(13, 30, 10, 100005);

Copy Code

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

orderTime.TdType = TdType.Time;

// Fractional Second Digits
orderTime.Scale = 6;

orderTime.ProviderSpecificValue = new TdTime(13, 30, 10, 100005);

Specifying Time as Literal

The syntax for the TIME Literal is TIME'hh:mi:ss.ssssss'. Time literals consist of the word TIME followed by character string literal representation of a Time value.

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 time value represented as Time 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(); // Orders after 1:30:45.123 PM cmd.CommandText = "SELECT Id, orderTime from Order where orderTime > TIME'13:30:45.123' "; using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("Id={0}, orderTime={1}", reader.GetInt64(0), reader.GetTime(1)); } } } }

Copy Code

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
    cmd.CommandText = "SELECT Id, orderTime from Order where orderTime > TIME'13:30:45.123' ";

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

Retrieving Time Data

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

The following methods and properties return the column or parameter value as a TdTime 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, orderTime from Order where orderTime > ?"; cmd.Parameters.Add("orderTime", TdType.Time); cmd.Parameters["orderTime"].Scale = 3; cmd.Parameters["orderTime"].Value = new TimeSpan(13, 30, 45, 123); using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("[TimeSpan] OrderTime = {0}", reader.GetTime(1).ToString()); Console.WriteLine("[TdTime] OrderTime = {0}", reader.GetTdTime(1).ToString()); } } } } /* Output: [TimeSpan] OrderTime = 13:40:10.7890000 [TdTime] OrderTime = 13:40:10.789 */

Copy Code

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

    TdCommand cmd = cn.CreateCommand();
    cmd.CommandText = "SELECT Id, orderTime from Order where orderTime > ?";
    cmd.Parameters.Add("orderTime", TdType.Time);
    cmd.Parameters["orderTime"].Scale = 3;
    cmd.Parameters["orderTime"].Value = new TimeSpan(13, 30, 45, 123);

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("[TimeSpan] OrderTime = {0}", reader.GetTime(1).ToString());
                Console.WriteLine("[TdTime] OrderTime = {0}", reader.GetTdTime(1).ToString());
            }
        }
    }
}

/* Output:
    [TimeSpan] OrderTime = 13:40:10.7890000
    [TdTime] OrderTime = 13:40:10.789
*/

Schema Collections

Schema Table

Configuring Parameters

Specifying Time as Literal

Retrieving Time Data

See Also