Conceptual Model Canonical to SQL Server Functions Mapping

  • Article

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)) = 1

Note   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