Send feedback on this topic.
Teradata.Client.Provider
Canonical Functions
.NET Data Provider for Teradata > Developer's Guide > ADO.NET Entity Provider for Teradata > Teradata Implementation Specifics > Canonical Functions
.NET CORE   This feature is not supported by the .NET Core implementation of the Data Provider.

Overview

Canonical functions are by definition a set of basic functions that all Entity Data Providers are to support. Canonical functions are independent of data sources, and the function signatures are all defined in terms of the Entity Data Model (EDM) data types. The Entity Provider for Teradata supports the Entity Framework 4.0 Canonical functions.

Canonical Functions Supported

Aggregate Canonical Functions Teradata Function Supported
Avg(expression) AVG(expression) YES
BigCount(expression) CAST(COUNT(expression) AS BIGINT) YES
Count(expression) CAST(COUNT(expression) AS INTEGER) YES
Max(expression) MAX(expression) YES
Min(expression) MIN(expression) YES
StDev(expression) STDDEV_SAMP(expression) YES
StDevP(expression) STDDEV_POP(expression) YES
Sum(expression) SUM(expression) YES
Var(expression) VAR_SAMP(expression) YES
VarP(expression) VAR_POP(expression) YES
Math Canonical Functions Teradata Function Supported
Abs(value) ABS(value) YES
Ceiling(value) CEILING(value) YES (note 2)
Floor(value) FLOOR(value) YES (note 2)
Round(value) ROUND(value) YES (note 5)
Round(value, digits) ROUND(value, digits) YES (note 5)
Power(value, exponent) POWER(value, exponent) YES (note 5)
Truncate(value, digits) TRUNC(value, digits) YES (note 5)
String Canonical Functions Teradata Function Supported
Concat(string1, string2) String1 || String2 YES
Contains(string, target) CASE
WHEN(POSITION((target) IN (string)) > 0 AND CHARACTER_LENGTH(string) > 0 AND CHARACTER_LENGTH(target) > 0)
THEN
CAST(1 AS BYTEINT) ELSE CAST(0 AS BYTEINT)
END
YES
EndsWith(string, target) CASE
WHEN(SUBSTRING((string) FROM CHARACTER_LENGTH(string) - CHARACTER_LENGTH(target) + 1) = (target)
AND CHARACTER_LENGTH(string) > 0 AND CHARACTER_LENGTH(target) > 0)
THEN CAST(1 AS BYTEINT) ELSE CAST(0 AS BYTEINT)
END
YES
IndexOf(string1, string2) CASE WHEN(CHARACTER_LENGTH(string1) = 0) THEN 0 ELSE POSITION((string1) IN (string2)) END YES
Left(string, length) LEFT(String, Length) YES (note 3 and
(note 7))
Length(string) CHARACTER_LENGTH(string) YES
LTrim(string) TRIM(LEADING FROM string) YES
Replace(string1, string2, string3) OREPLACE(string1, string2, string3) YES (note 5)
Reverse(string) REVERSE(string) YES (note 8)
Right(string, length) RIGHT(String, Length) YES (note 7)
RTrim(string) TRIM(TRAILING FROM string) YES
Substring (string, start, length) SUBSTRING(string FROM start FOR length) YES (note 3)
StartsWith(string, target) CASE
WHEN(POSITION((target) IN (string)) = 1 AND CHARACTER_LENGTH(string) > 0 AND CHARACTER_LENGTH(target) > 0)
THEN CAST(1 AS BYTEINT) ELSE CAST(0 AS BYTEINT)
END
YES
ToLower(string) LOWER(string) YES
ToUpper(string) UPPER(string) YES
Trim(string) TRIM(BOTH FROM string) YES
Date and Time Canonical Functions Teradata Function Supported
AddNanoSeconds(expression, number) NO
AddMicroSeconds(expression, number) expression + CAST( CAST( number AS DECIMAL(10,6))/ 1000000 AS INTERVAL SECOND(4,6)) YES (note 6)
AddMilliSeconds(expression, number) expression + CAST( CAST( number AS DECIMAL(10,3))/ 1000 AS INTERVAL SECOND(4,6)) YES (note 6)
AddSeconds(expression, number) expression + CAST( number AS INTERVAL SECOND(4,0)) YES (note 6)
AddMinutes(expression, number) expression + CAST( number AS INTERVAL MINUTE(4)) YES (note 6)
AddHours(expression, number) expression + CAST( number AS INTERVAL HOUR(4)) YES (note 6)
AddDays(expression, number) expression + CAST( number AS INTERVAL DAY(4)) YES (note 6)
AddMonths(expression, number) expression + CAST( number AS INTERVAL MONTH(4)) YES (note 6)
AddYears(expression, number) expression + CAST( number AS INTERVAL YEAR(4)) YES (note 6)
CreateDateTime(year, month, day, hour, minute, second) CAST( CAST( CAST( year AS INTEGER FORMAT'9999') AS CHAR(4)) || CAST( '-' AS CHAR(1)) ||
CAST( CAST( month AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( '-' AS CHAR(1)) ||
CAST( CAST( day AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( ' ' AS CHAR(1)) ||
CAST( CAST( hour AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( ':' AS CHAR(1)) ||
CAST( CAST( minute AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( ':' AS CHAR(1)) ||
CAST( CAST( CAST( second AS DOUBLE PRECISION) AS DECIMAL(8, 6) FORMAT'99.999999' ) AS CHAR(9)) AS TIMESTAMP)
YES
CreateDateTimeOffset(year, month, day, hour, minute, second, tzoffset) CAST( CAST( CAST( year AS INTEGER FORMAT'9999') AS CHAR(4)) || CAST( '-' AS CHAR(1)) ||
CAST( CAST( month AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( '-' AS CHAR(1)) ||
CAST( CAST( day AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( ' ' AS CHAR(1)) ||
CAST( CAST( hour AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( ':' AS CHAR(1)) ||
CAST( CAST( minute AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( ':' AS CHAR(1)) ||
CAST( CAST( CAST( second AS DOUBLE PRECISION) AS DECIMAL(8, 6) FORMAT'99.999999' ) AS CHAR(9)) ||
CASE WHEN ( tzoffset < 0) THEN CAST( '-' AS CHAR(1)) ELSE CAST( '+' AS CHAR(1)) END ||
CAST( CAST( tzoffset / 60 AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( ':' AS CHAR(1)) ||
CAST( CAST( tzoffset MOD 60 AS INTEGER FORMAT'99') AS CHAR(2)) AS TIMESTAMP WITH TIME ZONE)
YES
CreateTime(hour, minute, second) CAST( CAST( CAST( hour AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( ':' AS CHAR(1)) ||
CAST( CAST( minute AS INTEGER FORMAT'99') AS CHAR(2)) || CAST( ':' AS CHAR(1)) ||
CAST( CAST( CAST( second AS DOUBLE PRECISION) AS DECIMAL(8, 6) FORMAT'99.999999' ) AS CHAR(9)) AS TIME)
YES
CurrentDateTime() CAST(CURRENT_TIMESTAMP AS TIMESTAMP) YES
CurrentDateTimeOffset() CURRENT_TIMESTAMP YES
CurrentUtcDateTime() CAST(CURRENT_TIMESTAMP AS TIMESTAMP) YES
Day(expression) EXTRACT(DAY FROM expression) YES
DayOfYear(expression) TD_DAY_OF_YEAR(expression) YES (note 5)
DiffNanoSeconds(startexp, endexp) NO
DiffMilliseconds(startexp, endexp) CAST( (CAST( endexp AS DATE) - CAST( startexp AS DATE)) * 86400 * 1000 AS INTEGER) +
(CAST(EXTRACT(HOUR FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(HOUR FROM CAST( startexp AS TIMESTAMP)) AS INTEGER)) * 3600 * 1000 +
(CAST(EXTRACT(MINUTE FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(MINUTE FROM CAST( startexp AS TIMESTAMP)) AS INTEGER)) * 60 * 1000 +
(CAST(EXTRACT(SECOND FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(SECOND FROM CAST( startexp AS TIMESTAMP)) AS INTEGER)) * 1000 +
(CAST(EXTRACT(SECOND FROM CAST( endexp AS TIMESTAMP)) MOD 1 * 1000 AS INTEGER) - CAST( EXTRACT (SECOND FROM CAST( startexp AS TIMESTAMP)) MOD 1 * 1000 AS INTEGER))
YES
DiffMicroseconds(startexp, endexp) CAST( CAST( (CAST( endexp AS DATE) - CAST( startexp AS DATE)) * 86400 * 1000000 AS INTEGER) +
(CAST(EXTRACT(HOUR FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(HOUR FROM CAST( startexp AS TIMESTAMP)) AS INTEGER)) * 3600 * 1000000 +
(CAST(EXTRACT(MINUTE FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(MINUTE FROM CAST( startexp AS TIMESTAMP)) AS INTEGER)) * 60 * 1000000 +
(CAST(EXTRACT(SECOND FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(SECOND FROM CAST( startexp AS TIMESTAMP)) AS INTEGER)) * 1000000 +
(CAST( EXTRACT (SECOND FROM CAST( endexp AS TIMESTAMP)) MOD 1 * 1000000 AS INTEGER) - CAST( EXTRACT (SECOND FROM CAST( startexp AS TIMESTAMP)) MOD 1 * 1000000 AS INTEGER)) AS INTEGER)
YES
DiffSeconds(startexp, endexp) CAST( (CAST( endexp AS DATE) - CAST( startexp AS DATE)) * 86400 AS INTEGER) +
(CAST(EXTRACT(HOUR FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(HOUR FROM CAST( startexp AS TIMESTAMP)) AS INTEGER)) * 3600 +
(CAST(EXTRACT(MINUTE FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(MINUTE FROM CAST( startexp AS TIMESTAMP)) AS INTEGER)) * 60 +
(CAST(EXTRACT(SECOND FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(SECOND FROM CAST( startexp AS TIMESTAMP)) AS INTEGER))
YES
DiffMinutes(startexp, endexp) CAST( (CAST( endexp AS DATE) - CAST( startexp AS DATE)) * 1440 AS INTEGER) +
(CAST(EXTRACT(HOUR FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(HOUR FROM CAST( startexp AS TIMESTAMP)) AS INTEGER)) * 60 +
(CAST(EXTRACT(MINUTE FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(MINUTE FROM CAST( startexp AS TIMESTAMP)) AS INTEGER))
YES
DiffHours(startexp, endexp) CAST( (CAST( endexp AS DATE) - CAST( startexp AS DATE)) * 24 AS INTEGER) +
(CAST(EXTRACT(HOUR FROM CAST( endexp AS TIMESTAMP)) AS INTEGER) - CAST(EXTRACT(HOUR FROM CAST( startexp AS TIMESTAMP)) AS INTEGER))
YES
DiffDays(startexp, endexp) (CAST( endexp AS DATE) - CAST( startexp AS DATE)) YES
DiffMonths(startexp, endexp) (CAST(EXTRACT(YEAR FROM CAST( endexp AS DATE)) AS INTEGER) - CAST(EXTRACT(YEAR FROM CAST( startexp AS DATE)) AS INTEGER)) * 12 +
(CAST(EXTRACT(MONTH FROM CAST( endexp AS DATE)) AS INTEGER) - CAST(EXTRACT(MONTH FROM CAST( startexp AS DATE)) AS INTEGER))
YES
DiffYears(startexp, endexp) (CAST(EXTRACT(YEAR FROM CAST( endexp AS DATE)) AS INTEGER) - CAST(EXTRACT(YEAR FROM CAST( startexp AS DATE)) AS INTEGER)) YES
GetTotalOffsetMinutes(datetimeoffset) ((EXTRACT(TIMEZONE_HOUR FROM expression) * 60) + EXTRACT (TIMEZONE_MINUTE FROM expression)) YES
Hour(expression) EXTRACT(HOUR FROM expression) YES
Millisecond(expression) CAST(EXTRACT(SECOND FROM expression) MOD 1 * 1000 AS INTEGER) YES
Minute(expression) EXTRACT(MINUTE FROM expression) YES
Month(expression) EXTRACT(MONTH FROM expression) YES
Second(expression) CAST(EXTRACT(SECOND FROM expression) AS INTEGER) YES
TruncateTime(expression) TRUNC(expression) if expression is DATE
TO_TIMESTAMP_TZ(TO_CHAR(expression, 'YYYY-MM-DD "00:00:00.000000"TZR')) if expression is DATETIMEOFFSET
YES (note 5)
Year(expression) EXTRACT(YEAR FROM expression) YES
BitWise Canonical Functions Teradata Function Supported
BitwiseAnd(value1, value2) BITAND(value1, value2) YES (note 1)
BitWiseNot(value) BITNOT(value) YES (note 1)
BitWiseOR(value1, value2) BITOR(value1, value2) YES (note 1)
BitWiseXor(value1, value2) BITXOR(value1, value2) YES (note 1)
Other Canonical Functions Teradata Function Supported
NewGuid() NO (note 4)
  1. The Bitwise functions - BitWiseAnd, BitWiseNot, BitWiseOr and BitWiseXor are supported beginning in Teradata Database version 13.10. The ProviderManifestToken defined in the store schema definition file (SSDL) must be defined to support the Teradata Database version 13.10 or higher.

  2. The Math Canonical functions Ceiling and Floor are supported beginning in the Teradata Database version 13.10. The ProviderManifestToken in the SSDL file must be defined to support the Teradata Database version 13.10 or higher.

  3. The String Canonical functions Substring(string, start, length) and Left(string, length) have a limitation on the length parameter. If using the Teradata session mode, the length parameter may not be an Int64 data type for Teradata Database versions 14.00 or earlier. For more information on Teradata session modes, please see the Database Administration manual (Sessions and Accounts).

  4. The Other Canonical function NewGuid is not supported by Teradata. GUIDs are a data type not supported by Teradata. A NotSupported Exception is returned from calling this function.

  5. The Canonical functions Round, Power, Truncate, TruncateTime, Replace, DayOfYear are supported beginning the Teradata Database version 14.00. The ProviderManifestToken in the SSDL file must be defined to support the Teradata Database 14.00 or higher.

  6. All DateTime Canonical Add functions use Teradata Intervals to add the value to the DateTime, DateTimeOffset or Time (when applicable). Teradata Intervals (see the Teradata Data Types and Literals manual) are limited by a maximum precision of 4, which limits the maximum value in the Canonical Add function. Precision is the number of significant digits in the most significant field of the Interval. For example in the AddMonths function, the Interval Month is used to add months to the DateTime or DateTimeOffset. A maximum value of 9999 months (four significant digits of precision) may be added to a DateTime or DateTimeOffset in this example.

  7. The String Canonical functions Right and Left are supported in Teradata Database version 15.10 as direct fastpath functions. Using versions prior to Teradata Database 15.10, Right and Left are supported as a Substring function. The ProviderManifestToken in the SSDL file must be defined to support the Teradata Database version 15.10 or higher to use the new direct fastpath functions.

  8. The String Canonical function Reverse is supported beginning in Teradata Database version 15.10 as a direct fastpath function. The ProviderManifestToken in the SSDL file must be defined to support the Teradata Database version 15.10 or higher to use the new direct fastpath function.