概念モデル正規関数と SQL Server 関数とのマッピングConceptual Model Canonical to SQL Server Functions Mapping

このトピックでは、概念モデル正規関数を対応する SQL Server 関数にマップする方法について説明します。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 関数SQL Server functions
AddDays(expression)AddDays(expression) DATEADD(day, number, date)
AddHours(expression)AddHours(expression) DATEADD(hour, number, date)
AddMicroseconds(expression)AddMicroseconds(expression) DATEADD(microsecond, number, date)
AddMilliseconds(expression)AddMilliseconds(expression) DATEADD(millisecond, number, date)
AddMinutes(expression)AddMinutes(expression) DATEADD(minute, number, date)
AddMonths(expression)AddMonths(expression) DATEADD(month, number, date)
AddNanoseconds(expression)AddNanoseconds(expression) DATEADD(nanosecond, number, date)
AddSeconds(expression)AddSeconds(expression) DATEADD(second, number, date)
AddYears(expression)AddYears(expression) DATEADD(year, number, date)
CreateDateTime (年、月、日、時、分、秒)CreateDateTime(year, month, day, hour, minute, second) SQL Server 2000 と SQL Server 2005 の場合、書式指定された datetime 値がサーバー上に作成されます。For SQL Server 2000 and SQL Server 2005, a datetime formatted value is created on the server. SQL Server 2008 以上の場合、datetime2 値がサーバー上に作成されます。For SQL Server 2008 and later versions, a datetime2 value is created on the server.
CreateDateTimeOffset (年、月、日、時、分、秒、tzoffset)CreateDateTimeOffset(year, month, day, hour, minute, second, tzoffset) 書式指定された datetimeoffset 値がサーバー上に作成されます。A datetimeoffset formatted value is created on the server.

SQL Server 2000 と SQL Server 2005 ではサポートされません。Not supported in SQL Server 2000 or SQL Server 2005.
CreateTime (時間、分、秒)CreateTime(hour, minute, second) 書式指定された time 値がサーバー上に作成されます。A time formatted value is created on the server.

SQL Server 2000 と SQL Server 2005 ではサポートされません。Not supported in SQL Server 2000 or SQL Server 2005.
CurrentDateTime()CurrentDateTime() SysDateTime() (SQL Server 2008)。SysDateTime() in SQLServer 2008.

GetDate() (SQL Server 2000 および SQL Server 2005)。GetDate() in SQLServer 2000 and SQLServer 2005.
CurrentDateTimeOffset()CurrentDateTimeOffset() SysDateTimeOffset() (SQL Server 2008)。SysDateTimeOffset() in SQL Server 2008.

SQL Server 2000 と SQL Server 2005 ではサポートされません。Not supported in SQL Server 2000 or SQL Server 2005.
CurrentUtcDateTime()CurrentUtcDateTime() SysUtcDateTime() (SQL Server 2008)。SysUtcDateTime() in SQLServer 2008. GetUtcDate() (SQL Server 2000 および SQL Server 2005)。GetUtcDate() in SQL Server 2000 and SQL Server 2005.
DayOfYear(expression)DayOfYear(expression) DatePart(dayofyear, expression)
Day(expression)Day(expression) DatePart(day, expression)
DiffDays (startExpression、endExpression)DiffDays(startExpression, endExpression) DATEDIFF(day, startdate, enddate)
DiffHours (startExpression、endExpression)DiffHours(startExpression, endExpression) DATEDIFF(hour, startdate, enddate)
DiffMicroseconds (startExpression、endExpression)DiffMicroseconds(startExpression, endExpression) DATEDIFF(microsecond, startdate, enddate)
DiffMilliseconds (startExpression、endExpression)DiffMilliseconds(startExpression, endExpression) DATEDIFF(millisecond, startdate, enddate)
DiffMinutes (startExpression、endExpression)DiffMinutes(startExpression, endExpression) DATEDIFF(minute, startdate, enddate)
DiffNanoseconds (startExpression、endExpression)DiffNanoseconds(startExpression, endExpression) DATEDIFF(nanosecond, startdate, enddate)
DiffSeconds (startExpression、endExpression)DiffSeconds(startExpression, endExpression) DATEDIFF(second, startdate, enddate)
DiffYears (startExpression、endExpression)DiffYears(startExpression, endExpression) DATEDIFF(year, startdate, enddate)
GetTotalOffsetMinutes(DateTimeOffset)GetTotalOffsetMinutes(DateTimeOffset) DatePart(tzoffset, expression)
Hour(expression)Hour(expression) DatePart(hour, expression)
Millisecond(expression)Millisecond(expression) DatePart(millisecond, expression)
Minute(expression)Minute(expression) DatePart(minute, expression)
Month(expression)Month(expression) DatePart(month, expression)
Second(expression)Second(expression) DatePart(second, expression)
Truncate(expression)Truncate(expression) SQL Server 2000 と SQL Server 2005 で、切り捨てられたdatetime書式設定された値は、サーバー上に作成します。For SQL Server 2000 and SQL Server 2005, a truncated datetime formatted value is created on the server. SQL Server 2008 および以降のバージョンで、切り捨てられたdatetime2またはdatetimeoffset値が、サーバー上に作成します。For SQL Server 2008 and later versions, a truncated datetime2 or datetimeoffset value is created on the server.
Year(expression)Year(expression) DatePart(YEAR, expression)

集計関数Aggregate Functions

集計関数のマッピングを次の表に示します。The following table describes the aggregate functions mapping:

正規関数Canonical functions SQL Server 関数SQL Server functions
Avg(expression)Avg(expression) AVG(expression)
BigCount(expression)BigCount(expression) BIGCOUNT(expression)
Count(expression)Count(expression) COUNT(expression)
Min(expression)Min(expression) MIN(expression)
Max(expression)Max(expression) MAX(expression)
StDev(expression)StDev(expression) STDEV(expression)
StDevP(expression)StDevP(expression) STDEVP(expression)
Sum(expression)Sum(expression) SUM(expression)
Var(expression)Var(expression) VAR(expression)
VarP(expression)VarP(expression) VARP(expression)

数学関数Math functions

数学関数のマッピングを次の表に示します。The following table describes the math functions mapping:

正規関数Canonical functions SQL Server 関数SQL Server functions
Abs(value)Abs(value) ABS(value)
Ceiling(value)Ceiling(value) CEILING(value)
Floor(value)Floor(value) FLOOR(value)
Power(value)Power(value) POWER(value, exponent)
Round(value)Round(value) ROUND(value, digits, 0)
truncateTruncate ROUND(value , digits, 1)

文字列関数String Functions

文字列関数のマッピングを次の表に示します。The following table describes the string functions mapping:

正規関数Canonical functions SQL Server 関数SQL Server functions
Contains(string, target)Contains(string, target) CHARINDEX(target, string)
Concat (string1, string2)Concat(string1, string2) string1 + string2string1 + string2
EndsWith(string, target)EndsWith(string, target) CHARINDEX(REVERSE(target), REVERSE(string)) = 1

CHARINDEX関数が返されるfalse場合、stringは固定長文字列型の列に格納およびtargetは定数です。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. この問題を回避するには、データを固定長文字列の長さに合わせて切り詰めてから、文字列を EndsWith 関数に渡します。たとえば、EndsWith(TRIM(string), target) のように指定します。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 (ターゲット、string2)IndexOf(target, string2) CHARINDEX(target, string2)
左 (string1, 長さ)Left (string1, length) LEFT(string1, length)
Length (string)Length (string) LEN(string)
LTrim(string)LTrim(string) LTRIM(string)
右 (string1, 長さ)Right (string1, length) RIGHT (string1, length)
Trim(string)Trim(string) LTRIM(RTRIM(string))
置換 (string1、string2、string3)Replace (string1, string2, string3) REPLACE(string1, string2, string3)
Reverse (string)Reverse (string) REVERSE (string)
RTrim(string)RTrim(string) RTRIM(string)
StartsWith(string, target)StartsWith(string, target) CHARINDEX(target, string)
部分文字列 (文字列、開始、長さ)Substring(string, start, length) SUBSTRING(string, start, length)
ToLower(string)ToLower(string) LOWER(string)
ToUpper(string)ToUpper(string) UPPER(string)

ビット単位の関数Bitwise Functions

ビット単位の関数のマッピングを次の表に示します。The following table describes the bitwise functions mapping:

正規関数Canonical functions SQL Server 関数SQL Server functions
BitWiseAnd (value1, value2)BitWiseAnd (value1, value2) value1 & value2value1 & value2
Bitwisenot です (値)BitWiseNot (value) ~value~value
ビット単位の Or (value1, value2)BitWiseOr (value1, value2) value1 | value2value1 | value2
BitWiseXor (value1, value2)BitWiseXor (value1, value2) value1 ^ value2value1 ^ value2