Share via

EDM Canonical to SQL Server Functions Mapping

  • Article

This topic describes how Entity Data Model (EDM) canonical functions map to the corresponding SQL Server functions. .

Data and Time Functions

The following table describes the date and time functions mapping:

Canonical functions SQL Server functions

Year(expression)

DatePart(YEAR, expression)

Month(expression)

DatePart(MONTH, expression)

Day(expression)

DatePart(DAY, expression)

Hour(expression)

DatePart(HOUR, expression)

Minute(expression)

DatePart(MINUTE, expression)

Second(expression)

DatePart(SECOND, expression)

Millisecond(expression)

DatePart(MILLISECOND, expression)

GetTotalOffsetMinutes(DateTimeOffset)

DatePart(TZOFFSET, expression)

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

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)

Sum(expression)

SUM(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)

Round(value)

ROUND(value, 0)

String Functions

The following table describes the string functions mapping:

Canonical functions SQL Server functions

Concat(string1, string2)

string1 + string2

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)

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