Table 4: Function Translations

Access Function

SQL Server Equivalent

iif (boolean-expression, true-expression, false-expression)

Case when boolean-expression then true-expression else false-expression end

Clng (x), cdbl(x), .

Convert ( typename, x)

trim(x)

Ltrim ( rtrim(x))

fix(date-expression)

convert(datetime,convert(varchar, date-expression, 101))

fix(float-expression)

Floor(float-expression)

Left(x, n)

Substring(x, 1, n)

Choose (x, r1, r2, . Rn)

Case x when 1 then r1 when 2 then r2 . When n then rn end

Weekday(x)

Datepart(weekday,x)

Format (x, "General Date")

convert ( varchar, x, 100)

Format (x, "long date")

convert ( varchar, x, 109)

Format (x, "short date")

convert ( varchar, x, 101)

Format (x, "hh:mm")

substring (convert (varchar, x, 108), 1, 4)

Dateadd ("day", 5, x)

Dateadd (dd, 5, x)

Len (x)

Datalength (x)

Lcase (x) and ucase (x)

Use Lower (x) and Upper (x)

Sgn (x)

Sign (x)