Schema Collections

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

Column Name	Description
COLUMN_TYPE or DATA_TYPE	Set to DATE.
FORMAT	Format assigned to the Advanced SQL Engine object.
PROVIDERDBTYPE	It is set to TdType.Date.

Column Name

Description

COLUMN_TYPE

DATA_TYPE

Set to DATE.

FORMAT

Format assigned to the Advanced SQL Engine object.

PROVIDERDBTYPE

It is set to TdType.Date.

Schema Table

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

Column Name	Description
DataType	System.Type object for the System.DateTime type.
ProviderType	Set to TdType.Date.
Format	Format assigned to the SQL Engine object.
ProviderSpecificDataType	System.Type object for the TdDate structure.

Configuring Parameters

The following example shows how to configure a Date parameter using DbType type declaration and a BCL value. The value is set to a System.DateTime instance and the Time components (Hour, Minute, Second and Millisecond) are set to zero. The Data Provider ignores the Time components of the System.DateTime when DbType is set to DbType.Date.

C#	Copy Code
TdParameter orderDate = new TdParameter(); orderDate.ParameterName = "orderDate"; orderDate.DbType = DbType.Date; orderDate.Value = new System.DateTime(2011, 1, 1);

The following example shows how to configure a Date parameter using TdType type declaration and a Provider Specific value.

C#	Copy Code
TdParameter orderDate = new TdParameter(); orderDate.ParameterName = "orderDate"; orderDate.TdType = TdType.Date; orderDate.ProviderSpecificValue = new TdDate(2011, 1, 1);

Specifying Date as Literal

The syntax for the DATE Literal is DATE'YYYY-MM-DD'. Date literals consist of the word DATE followed by character string literal representation of a date value with YYYY-MM-DD format.

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

The following example shows January 1, 2011 date value represented as Date-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 cmd.CommandText = "SELECT Id, orderDate from Order where orderDate > DATE'2011-01-30' "; using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("Id={0}, orderDate={1}", reader.GetInt64(0), reader.GetDate(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
    cmd.CommandText = "SELECT Id, orderDate from Order where orderDate > DATE'2011-01-30' ";

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

Retrieving Date 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 TdDate 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, orderDate from Order where orderDate > ?"; cmd.Parameters.Add("orderDate", TdType.Date); cmd.Parameters["orderDate"].Value = new DateTime(1990, 1, 30); using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("[DateTime] OrderDate = {0}", reader.GetDateTime(1).ToString()); Console.WriteLine("[TdDate] OrderDate = {0}", reader.GetTdDate(1).ToString()); } } } } /* Output: [DateTime] OrderDate = 9/17/1997 12:00:00 AM [TdDate] OrderDate = 1997-09-17 [DateTime] OrderDate = 12/24/1997 12:00:00 AM [TdDate] OrderDate = 1997-12-24 [DateTime] OrderDate = 12/18/1996 12:00:00 AM [TdDate] OrderDate = 1996-12-18 */