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: |
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: | |
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: |
Interval Month (precision) | Period of time in Months. The precision range is from 1 to 4.
Precision controls the range of valid values: | |
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:
Notable differences between the SQL data Types and BCL Types are:
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.
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; } |
Interval Day To Hour Data Type
Interval Day To Minute Data Type
Interval Day To Second Data Type
Interval Hour To Minute Data Type
Interval Hour To Second Data Type
Interval Year To Month Data Type
Formatting and Parsing of Strings
TdConnectionStringBuilder.EnableTdIntervals