Send feedback on this topic.
Teradata.Client.Provider
Interval Data Types Overview
.NET Data Provider for Teradata > Developer's Guide > Data Types > Interval Data Types Overview

The Advanced SQL Engine has 10 Day-to-Second and 3 Year-to-Month Interval data types:

SQL Data Type Description
Interval Day To Second Types Interval Day (precision)

Period of time in Days. The precision range is from 1 to 4.

Precision controls the range of valid values:
1) 1 to 9 Days
2) 1 to 99 Days
3) 1 to 999 Days
4) 1 to 9999 Days

Interval Day (precision) To Hour Period of time in Days and Hours.
Interval Day (precision) To Minute Period of time in Days, Hours and Minutes.
Interval Day (precision) To Second (fractional_second_digits) Period of time in Days, Hours, Minutes, Seconds and Fractional Seconds. The fractional_second_digits range is from 0 to 6.
Interval Hour (precision) Period of time in Hours. The precision range is from 1 to 4.

Precision controls the range of valid values:
1) 1 to 9 Hours
2) 1 to 99 Hours
3) 1 to 999 Hours
4) 1 to 9999 Hours
Interval Hour (precision) To Minute Period of time in Hours and Minutes.
Interval Hour (precision) To Second (fractional_second_digits) Period of time in Hours, Minutes, Seconds, and Fractional Seconds.  The fractional_second_digits range is from 0 to 6.
Interval Minute (precision)

Period of time in Minutes. The precision range is from 1 to 4.

Precision controls the range of valid values:
1) 1 to 9 Minutes
2) 1 to 99 Minutes
3) 1 to 999 Minutes
4) 1 to 9999 Minutes

Interval Minute (precision) To Second(fractional_second_digits) Period of time in Minutes, Seconds and Fractional Seconds. The fractional_second_digits range is from 0 to 6.
Interval Second (precision, fractional_second_digits) Period of time in Seconds and Fractional Seconds. The precision range is from 1 to 4. The fractional_second_digits range is from 0 to 6.

Precision controls the range of Seconds:
1) 1 to 9 Seconds
2) 1 to 99 Seconds
3) 1 to 999 Seconds
4) 1 to 9999 Seconds

Fractional_Second_Digits controls the range of Fractional Seconds:
0) No fractional seconds component
1) .0 to .9 seconds
2) .00 to .99 seconds
3) .000 to .999 seconds
4) .0000 to .9999 seconds
5) .00000 to .99999 seconds
6) .000000 to .999999 seconds
Interval Year to Month Types Interval Year (precision)

Period of time in Years. The precision range is from 1 to 4.

Precision controls the range of valid values:
1) 1 to 9 Years
2) 1 to 99 Years
3) 1 to 999 Years
4) 1 to 9999 Years

Interval Month (precision) Period of time in Months. The precision range is from 1 to 4.

Precision controls the range of valid values:
1) 1 to 9 Months
2) 1 to 99 Months
3) 1 to 999 Months
4) 1 to 9999 Months

Interval Year (precision) To Month Period of time in Years and Months. The precision range is from 1 to 4.

The  "SQL Data Type and Literals" manual describes each type in detail. This sections shows how the SQL Engine Interval data types map to the .NET type system.

The Base Class Library (BCL) has one Interval data type:

  1. System.TimeSpan

Notable differences between the SQL data Types and BCL Types are:

  1. BCL does not have a type corresponding to the SQL Interval Year to Month data types.
  2. The best match for System.TimeSpan data type is the SQL "Interval Day To Second" data type. However System.TimeSpan type can support the remaining 9 Interval Day To Seconds SQL data types.
  3. BCL supports 7 fractional second digits while the SQL Engine supports 6 fractional second digits.
  4. BCL supports 10,675,199 days while the SQL Engine supports 9,999 days.

The .NET Data Provider for Teradata has thirteen Interval Provider Specific Types and it also converts the SQL Interval data types to BCL types:

SQL Type BCL Type Provider Specific Type
Interval Day (precision) TimeSpan TdIntervalDay
Interval Day (precision) To Hour TimeSpan TdIntervalDayToHour
Interval Day (precision) To Minute TimeSpan TdIntervalDayToMinute
Interval Day (precision) To Second (fractional_second_digits) TimeSpan TdIntervalDayToSecond
Interval Hour (precision) TimeSpan TdIntervalHour
Interval Hour (precision) To Minute TimeSpan TdIntervalHourToMinute
Interval Hour (precision) To Second (fractional_second_digits) TimeSpan TdIntervalHourToSecond
Interval Minute (precision) TimeSpan TdIntervalMinute
Interval Minute (precision) To Second (fractional_second_digits) TimeSpan TdIntervalMinuteToSecond
Interval Second (precision, fractional_second_digits) TimeSpan TdIntervalSecond
Interval Year (precision) String TdIntervalYear
Interval Month (precision) String TdIntervalMonth
Interval Year (precision) To Month String TdIntervalYearToMonth

Each Provider Specific Interval data type supports the following:

The Provider Specific Types and BCL Types can be used as parameters in all database operations, and data can be retrieved using these types by invoking the corresponding TdDataReader methods.

Example

C#
Copy Code
public void StoredProcedureExample(TdCommand command)
{
     // The procedure will have an output parameter that is an interval
     command.CommandText = "StoredProcedureExample";
     command.CommandType = CommandType.StoredProcedure;

     // StoredProcedureExample listing
     // replace procedure dotnetvs.StoredProcedureExample (out p1 Interval Year(4) To Month)
     // begin
     // declare Today Date;
     // declare StartDate Date;
     // select Date into Today;
     // set StartDate = Date '2008-09-21';
     // set p1 = (Today - StartDate) Year(4) To Month;
     // end;

     // The parameter is a IntervalYearToMonth TdType
     command.Parameters.Add(null, TdType.IntervalYearToMonth 0, ParameterDirection.Output,
         true, 0, 0, null, DataRowVersion.Default, null);

     command.ExecuteNonQuery();

     // GETTING THE DATA FOR THE PARAMETER USING THE CONNECTION STRING
     // ATTRIBUTE EnableTdIntervals

     // Connection String Attribute set before opening connection -
     // EnableTdIntervals=true

     // ProviderSpecificValue is accessed to retrieve the data being
     // returned for the output parameter as a provider specific type.

     TdIntervalYearToMonth resultAsInterval = command.Parameters[0].ProviderSpecificValue;

     // The Value property always returns the data as the .NET System type
     // regardless of the EnableTdIntervals attribute setting.

     String resultAsString = command.Parameters[0].Value;

     // Connection String Attribute set before opening connection -
     // EnableTdIntervals=false

     // ProviderSpecificValue is accessed to retrieve the data being
     // returned for the output parameter as a .NET System type for backward compatibility.

     String resultAsString = command.Parameters[0].ProviderSpecificValue;

     //The Value property always returns the data as the .NET System type
     //regardless of the EnableTdIntervals attribute setting.

     resultAsString = command.Parameters[0].Value;

}

In This Section

Interval Day Data Type

Interval Day To Hour Data Type

Interval Day To Minute Data Type

Interval Day To Second Data Type

Interval Hour Data Type

Interval Hour To Minute Data Type

Interval Hour To Second Data Type

Interval Second Data Type

Interval Year Data Type

Interval Month Data Type

Interval Year To Month Data Type

Restrictions

Formatting and Parsing of Strings

Arithmetic Operations

Implicit Conversion Support

Connection String Attributes

See Also

TdConnectionStringBuilder.EnableTdIntervals