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.
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) |
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.
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.
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).
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.
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.
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.
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.
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.