Conceptual Model Canonical to SQL Server Functions Mapping

 

This topic describes how conceptual model canonical functions map to the corresponding SQL Server 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)

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)

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)

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)

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

Show: