Schema Collections

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

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

Schema Table

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

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

Configuring Parameters

The following example shows how to configure a Timestamp parameter using DbType type declaration and a BCL 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. The SQL Engine will return an error when the TdParameter.Scale is greater than the target object declaration.

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

C#	Copy Code
TdParameter orderTimestamp = new TdParameter(); orderTimestamp.ParameterName = "orderTimestamp"; orderTimestamp.DbType = DbType.DateTime; // Fractional second digits. orderTimestamp.Scale = 6; orderTimestamp.Value = new DateTime(2011, 1, 1, 13, 30, 10);

Copy Code

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

orderTimestamp.DbType = DbType.DateTime;

// Fractional second digits.
orderTimestamp.Scale = 6;

orderTimestamp.Value = new DateTime(2011, 1, 1, 13, 30, 10);

The following example shows how to configure a Timestamp 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 TdTimestamp.Scale property when the TdParameter.Scale, TdParameter.Precision and TdParameter.Size properties are set to zero.

C#	Copy Code
TdParameter orderTimestamp = new TdParameter(); orderTimestamp.ParameterName = "orderTimestamp"; orderTimestamp.TdType = TdType.Timestamp; // Fractional second digits. orderTimestamp.Scale = 6; orderTimestamp.ProviderSpecificValue = new TdTimestamp(2011, 1, 1, 13, 30, 10);

Copy Code

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

orderTimestamp.TdType = TdType.Timestamp;

// Fractional second digits.
orderTimestamp.Scale = 6;

orderTimestamp.ProviderSpecificValue = new TdTimestamp(2011, 1, 1, 13, 30, 10);

Specifying Timestamp as Literal

The syntax for the Timestamp Literal is TIMESTAMP'YYYY-MM-DD hh:mi:ss.ssssss'. Timestamp literals consist of the word TIMESTAMP followed by character string literal representation of a Timestamp. 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 January 30, 2011 1:30:45 PM represented as Timestamp 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 January 30, 2011 1:30:45 PM cmd.CommandText = "SELECT Id, orderTimestamp from Order where orderTimestamp > TIMESTAMP'2011-01-30 13:30:45' "; using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("Id={0}, orderTimestamp={1}", reader.GetInt64(0), reader.GetDateTime(1)); } } } }

Copy Code

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

    // Orders after January 30, 2011 1:30:45 PM
    cmd.CommandText = "SELECT Id, orderTimestamp from Order where orderTimestamp > TIMESTAMP'2011-01-30 13:30:45' ";

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

Retrieving Timestamp Data

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

The following methods and properties return the column or parameter value as a TdTimestamp 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, orderTimestamp from Order where orderTimestamp > ?"; cmd.Parameters.Add("orderTimestamp", TdType.Timestamp); cmd.Parameters["orderTimestamp"].Scale = 0; cmd.Parameters["orderTimestamp"].Value = new DateTime(1990, 1, 30, 13, 30, 45); using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("[DateTime] OrderTimestamp = {0}", reader.GetDateTime(1).ToString()); Console.WriteLine("[TdTimestamp] OrderTimestamp = {0}", reader.GetTdTimestamp(1).ToString()); } } } } /* Output: [DateTime] OrderTimestamp = 9/17/1997 12:00:00 AM [TdTimestamp] OrderTimestamp = 1997-09-17 00:00:00 [DateTime] OrderTimestamp = 12/24/1997 1:30:22 AM [TdTimestamp] OrderTimestamp = 1997-12-24 01:30:22 */

Copy Code

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

    TdCommand cmd = cn.CreateCommand();
    cmd.CommandText = "SELECT Id, orderTimestamp from Order where orderTimestamp > ?";
    cmd.Parameters.Add("orderTimestamp", TdType.Timestamp);
    cmd.Parameters["orderTimestamp"].Scale = 0;
    cmd.Parameters["orderTimestamp"].Value = new DateTime(1990, 1, 30, 13, 30, 45);

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("[DateTime] OrderTimestamp = {0}", reader.GetDateTime(1).ToString());
                Console.WriteLine("[TdTimestamp] OrderTimestamp = {0}", reader.GetTdTimestamp(1).ToString());
            }
        }
    }
}

/* Output:
    [DateTime] OrderTimestamp = 9/17/1997 12:00:00 AM
    [TdTimestamp] OrderTimestamp = 1997-09-17 00:00:00
    [DateTime] OrderTimestamp = 12/24/1997 1:30:22 AM
    [TdTimestamp] OrderTimestamp = 1997-12-24 01:30:22
*/

Schema Collections

Schema Table

Configuring Parameters

Specifying Timestamp as Literal

Retrieving Timestamp Data

See Also