The following sections describe the restrictions when using the Teradata Provider Interval Types.

Teradata Database 6.2 and prior versions cannot distinguish between Interval Minute and Interval Month

If the data being returned is of type Interval Minute or Interval Month the provider will always return a TdIntervalMonth as the default.

For example, suppose the data of a column that is defined as an Interval Month or Interval Minute is being retrieved. If the connection string attribute EnableTdIntervals is true, the provider will return a TdIntervalMonth when accessing the TdDataReader.GetProviderSpecificValue property.

It is recommended to set the Connection String Attribute EnableTdIntervals to false to return Interval data types as a string when using TdIntervalMinute with a Teradata Database prior to version 6.2.

Using System.TimeSpan as a Parameter For Day-Time Intervals

Day-Time Intervals are defined as:

• TdIntervalDayToSecond
• TdIntervalDayToMinute
• TdIntervalDayToHour
• TdIntervalDay
• TdIntervalHourToSecond
• TdIntervalHourToMinute
• TdIntervalHour
• TdIntervalMinuteToSecond
• TdIntervalMinute
• TdIntervalSecond

When the provider is connected to a Teradata Database prior to 12.0 there is a problem when using DbParameters and System.TimeSpan to represent a Day-Time Interval. When using DbParameters to send Intervals represented as System.TimeSpan to Teradata, the DbParameter.DbType property must be set to DbType.Object. There is not enough metadata available for a Teradata Database prior to 12.0 to recognize the type of data being sent. So the operation will fail.

In the following example, the code will fail when executed against a Teradata Database prior to 12.0. It will succeed if executed against a version of the Teradata Database that is 12.0 or greater.

public void TimeSpanExample(TdCommand command) 
{
     // Use a TimeSpan to insert into an interval column
     // created with Create Table IntervalDTM
     // (col0 integer, col2 Interval Day(4) To Minute)
     command.CommandText = "Insert into IntervalDTM (?,?)";

     TdParameter param = new TdParameter();
     param.TdType = TdType.Integer;
     param.Value = 1;
     param.ParameterName = "p1";
     command.Parameters.Add(param);

     param = new TdParameter();
     param.DbType = DbType.Object;
     // TimeSpan = 10 days, 4 hours and 2 minutes
     param.Value = new TimeSpan(10, 4, 2, 0);
     param.ParameterName = "p2";
     command.Parameters.Add(param);

     command.ExecuteNonQuery();

} 

To workaround this problem, parameters must be defined as TdParameter and the TdParameter.TdType must be set to the corresponding Day-Time Interval type. This will provide a Teradata Database prior to 12.0 with enough information so that the operation will succeed.

The following is an example that uses TdParameter to send a System.TimeSpan that represents a Day-Time Interval to a Teradata Database prior to 12.0.

public void TimeSpanWorkAroundExample(TdCommand command) 
{
     // Construct an interval using a TimeSpan
     // created with Create Table IntervalDTM
     // (col0 integer, col2 Interval Day(4) To Minute)
     command.CommandText = "Insert into IntervalDTM (?,?)";

     TdParameter param = new TdParameter();
     param.TdType = TdType.Integer;
     param.Value = 1;
     param.ParameterName = "p1";
     command.Parameters.Add(param);

     param = new TdParameter();
     param.TdType = TdType.IntervalDayToMinute;
     // TimeSpan = 10 days, 4 hours and 2 minutes
     param.Value = new TdIntervalDayToMinute(new TimeSpan(10, 4, 2, 0));
     param.ParameterName = "p2";
     command.Parameters.Add(param);

     command.ExecuteNonQuery();

} 

Cultural settings are not supported in any of the intervals.

None of the provider specific Inteval Types support any cultural settings.

See Also

Interval Formatting and Parsing of Strings