Conceptual Model Canonical to SQL Server Functions Mapping
This topic describes how conceptual model canonical functions map to the corresponding SQL Server functions.
Date and Time Functions
The following table describes the date and time functions mapping:
| Canonical functions | SQL Server functions |
|---|---|
| AddDays(expression) | DATEADD(day, number, date) |
| AddHours(expression) | DATEADD(hour, number, date) |
| AddMicroseconds(expression) | DATEADD(microsecond, number, date) |
| AddMilliseconds(expression) | DATEADD(millisecond, number, date) |
| AddMinutes(expression) | DATEADD(minute, number, date) |
| AddMonths(expression) | DATEADD(month, number, date) |
| AddNanoseconds(expression) | DATEADD(nanosecond, number, date) |
| AddSeconds(expression) | DATEADD(second, number, date) |
| AddYears(expression) | DATEADD(year, number, date) |
| CreateDateTime(year, month, day, hour, minute, second) | For SQL Server 2000 and SQL Server 2005, a datetime formatted value is created on the server. For SQL Server 2008 and later versions, a datetime2 value is created on the server. |
| CreateDateTimeOffset(year, month, day, hour, minute, second, tzoffset) | A datetimeoffset formatted value is created on the server.Not supported in SQL Server 2000 or SQL Server 2005. |
| CreateTime(hour, minute, second) | A time formatted value is created on the server.Not supported in SQL Server 2000 or SQL Server 2005. |
| CurrentDateTime() | SysDateTime() in SQLServer 2008.GetDate() in SQLServer 2000 and SQLServer 2005. |
| CurrentDateTimeOffset() | SysDateTimeOffset() in SQL Server 2008.Not supported in SQL Server 2000 or SQL Server 2005. |
| CurrentUtcDateTime() | SysUtcDateTime() in SQLServer 2008. GetUtcDate() in SQL Server 2000 and SQL Server 2005. |
| DayOfYear(expression) | DatePart(dayofyear, expression) |
| Day(expression) | DatePart(day, expression) |
| DiffDays(startExpression, endExpression) | DATEDIFF(day, startdate, enddate) |
| DiffHours(startExpression, endExpression) | DATEDIFF(hour, startdate, enddate) |
| DiffMicroseconds(startExpression, endExpression) | DATEDIFF(microsecond, startdate, enddate) |
| DiffMilliseconds(startExpression, endExpression) | DATEDIFF(millisecond, startdate, enddate) |
| DiffMinutes(startExpression, endExpression) | DATEDIFF(minute, startdate, enddate) |
| DiffNanoseconds(startExpression, endExpression) | DATEDIFF(nanosecond, startdate, enddate) |
| DiffSeconds(startExpression, endExpression) | DATEDIFF(second, startdate, enddate) |
| DiffYears(startExpression, endExpression) | DATEDIFF(year, startdate, enddate) |
| GetTotalOffsetMinutes(DateTimeOffset) | DatePart(tzoffset, expression) |
| Hour(expression) | DatePart(hour, expression) |
| Millisecond(expression) | DatePart(millisecond, expression) |
| Minute(expression) | DatePart(minute, expression) |
| Month(expression) | DatePart(month, expression) |
| Second(expression) | DatePart(second, expression) |
| Truncate(expression) | For SQL Server 2000 and SQL Server 2005, a truncated datetime formatted value is created on the server. For SQL Server 2008 and later versions, a truncated datetime2 or datetimeoffset value is created on the server. |
| Year(expression) | DatePart(YEAR, expression) |
Aggregate Functions
The following table describes the aggregate functions mapping:
| Canonical functions | SQL Server functions |
|---|---|
| Avg(expression) | AVG(expression) |
| BigCount(expression) | BIGCOUNT(expression) |
| Count(expression) | COUNT(expression) |
| Min(expression) | MIN(expression) |
| Max(expression) | MAX(expression) |
| StDev(expression) | STDEV(expression) |
| StDevP(expression) | STDEVP(expression) |
| Sum(expression) | SUM(expression) |
| Var(expression) | VAR(expression) |
| VarP(expression) | VARP(expression) |
Math functions
The following table describes the math functions mapping:
| Canonical functions | SQL Server functions |
|---|---|
| Abs(value) | ABS(value) |
| Ceiling(value) | CEILING(value) |
| Floor(value) | FLOOR(value) |
| Power(value) | POWER(value, exponent) |
| Round(value) | ROUND(value, digits, 0) |
| Truncate | ROUND(value , digits, 1) |
String Functions
The following table describes the string functions mapping:
| Canonical functions | SQL Server functions |
|---|---|
| Contains(string, target) | CHARINDEX(target, string) |
| Concat(string1, string2) | string1 + string2 |
| EndsWith(string, target) | CHARINDEX(REVERSE(target), REVERSE(string)) = 1Note The CHARINDEX function returns false if the string is stored in a fixed length string column and target is a constant. In this case, the entire string is searched, including any padding trailing spaces. A possible workaround is to trim the data in the fixed length string before passing the string to the EndsWith function, as in the following example: EndsWith(TRIM(string), target) |
| IndexOf(target, string2) | CHARINDEX(target, string2) |
| Left (string1, length) | LEFT(string1, length) |
| Length (string) | LEN(string) |
| LTrim(string) | LTRIM(string) |
| Right (string1, length) | RIGHT (string1, length) |
| Trim(string) | LTRIM(RTRIM(string)) |
| Replace (string1, string2, string3) | REPLACE(string1, string2, string3) |
| Reverse (string) | REVERSE (string) |
| RTrim(string) | RTRIM(string) |
| StartsWith(string, target) | CHARINDEX(target, string) |
| Substring(string, start, length) | SUBSTRING(string, start, length) |
| ToLower(string) | LOWER(string) |
| ToUpper(string) | UPPER(string) |
Bitwise Functions
The following table describes the bitwise functions mapping:
| Canonical functions | SQL Server functions |
|---|---|
| BitWiseAnd (value1, value2) | value1 & value2 |
| BitWiseNot (value) | ~value |
| BitWiseOr (value1, value2) | value1 | value2 |
| BitWiseXor (value1, value2) | value1 ^ value2 |