Function 

Description 

Result Data Types 

Examples 

AVG 

AVG(expression) 

Numeric, Money 

Returns the average of the values in a group. Null values are ignored. 

Numeric, Money 

AVG([Retail].[dbo].[Orders].[Freight]) 

COUNT 

COUNT(expression) 

Any data type except Image and Lob. 

Returns the number of items in a group. 

Numeric. 

COUNT([Retail].[dbo].[Orders].[OrderID]) 

MAX 

MAX(expression) 

Any data type except Image and Lob. 

Returns the maximum value in a group. 

The same data type as expression. 

MAX([Retail].[dbo].[Orders].[Freight]) 

MIN 

MIN(expression) 

Any data type except Image and Lob. 

Returns the minimum value in a group. 

The same data type as expression. 

MIN([Retail].[dbo].[Orders].[Freight]) 

SUM 

SUM(expression) 

Numeric, Money. 

Returns the sum of all the values in a group. Null values are ignored. 

The same data type as expression. 

SUM([Retail].[dbo].[Orders].[Freight]) 

LEN 

LEN(expression) 

Text. 

Returns the number of characters of the given text expression, excluding trailing blanks. 

Numeric. 

LEN([Retail].[dbo].[Orders].[ShipAddress]) 

ROUND 

ROUND(expression) 

Numeric, Money. 

Returns the expression rounded to the specified length or precision. 

The same data type as expression. 

ROUND([Retail].[dbo].[Orders].[Freight],0) 

CONCAT 

CONCAT(expression, expression [, ...]) 

Text. 

Returns the concatenation of all the parameters in that exact order. 

Text. 

CONCAT('ab','cd',[SHIPCOUNTRY]) 

GETDATE 

GETDATE() 

N/A. 

Returns the current system date and time. 

Datetime. 

GETDATE() 

DATEADD 

DATEADD(datepart, number, date) 

datepart: the part of the date.  

(See table List of Dateparts and Abbreviations below)

number: the value used to increment datepart.

date: an expression that returns a datetime value. 

Returns a new datetime value based on adding an interval to the specified date. 

Datetime. 

DATEADD(day,3,[DueDate]) 

DATEDIFF 

DATEDIFF(datepart, startdate, enddate) 

datepart: the part of the date.  
(See table List of Dateparts and Abbreviations below)

startdate, enddate: expressions that return datetime values. 

Returns the number of date and time boundaries crossed between two specified dates. 

Numeric. 

DATEDIFF(day,[OrderDate],[ShipDate])

 

DATEPART 

DATEPART(datepart, date) 

datepart: the part of the date.  
(See table List of Dateparts and Abbreviations below)

date: an expression that returns a datetime value. 

Returns a number representing the specified datepart of the specified date. 

Numeric. 

DATEPART(DAY,[Retail].[dbo].[Orders].[OrderDate]) 

CONVERT 

CONVERT(data_type, expression) 

data_type: any data type.

expression: any expression. 

Explicitly converts an expression of one data type to another, similar to CAST..AS. 

The same data type as data_type. 

CONVERT(TEXT,[Retail].[dbo].[Orders].[OrderDate]) 

CAST..AS 

CAST(expression AS data_type) 

data_type: any data type.

expression: any expression. 

Explicitly converts an expression of one data type to another, similar to CONVERT. 

The same data type as data_type. 

CAST([Retail].[dbo].[Orders].[OrderID] AS TEXT) 

ISNULL 

ISNULL(check_expression, replacement_expression) 

check_expression and replacement_expression: any data type. 

Returns the value of check_expression if it is not NULL; otherwise, returns the value of replacement_expression. 

The same data type as expression. 

ISNULL([Retail].[dbo].[Orders].[ShipRegion] , 'No Region') 

BETWEEN..AND 

BETWEEN(expression, begin_expression, end_expression) 

Any data type except Image and Lob. 

Returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression, otherwise returns FALSE. 

Boolean. 

CASE WHEN (BETWEEN ([Retail].[dbo].[Orders].[EmployeeID],1 , 3)) THEN 1000 else [Retail].[dbo].[Orders].[EmployeeID] END 

AND 

boolean_expression AND boolean_expression 

Boolean. 

Returns TRUE when both expressions are TRUE, otherwise returns FALSE. 

Boolean. 

CASE WHEN ([Retail].[dbo].[Orders].[EmployeeID] = 1 AND [Retail].[dbo].[Orders].[CustomerID] = 'DELDG') THEN 1000 else [Retail].[dbo].[Orders].[EmployeeID] end 

OR 

boolean_expression AND boolean_expression 

Boolean. 

Returns TRUE when either expression is TRUE, otherwise returns FALSE. 

Boolean. 

CASE WHEN ([Retail].[dbo].[Orders].[EmployeeID] = 1 OR [Retail].[dbo].[Orders].[EmployeeID] = 2) THEN 1000 else [Retail].[dbo].[Orders].[EmployeeID] end 

DISTINCT 

DISTINCT (expression) or DISTINCT expression 

Any data type except Image and Lob. 

Returns unique values. 

The same data type as expression. 

COUNT(DISTINCT([Northwind].[dbo].[Orders].[ShipCity])) 

IFF 

IFF (boolean_expression, true_expression,false_expression) 

boolean_expression: Boolean.

true_expression, false_expression: any data type except Image and Lob. 

Returns the value of true_expression when boolean_expression is TRUE, otherwise returns the value of false_expression. 

The highest precedence data type from data types of true_expression and false_expression. 

IIF([Retail].[dbo].[Orders].[EmployeeID] = 2, 200, [Retail].[dbo].[Orders].[EmployeeID]) 

IF..THEN..ELSE..END 

IF (boolean_expression) THEN (true_expression)  
[ELSE (false_expression)]  
END 

boolean_expression: Boolean.

true_expression, false_expression: any data type except Image and Lob. 

Returns the value of true_expression when boolean_expression is TRUE, otherwise returns the value of false_expression. 

The highest precedence data type from data types of true_expression and false_expression. 

IF ([northwind].[dbo].[Orders].[EmployeeID] < 3) then 'Less' else ( IF (BETWEEN ([northwind].[dbo].[Orders].[EmployeeID] , 3, 6)) then 'More' else 'Most' END) END 

CASE WHEN..THEN..ELSE..END 

CASE WHEN (when_expression)  

THEN (result_expression) […n]  
[ELSE (else_result_expression)] END 

Any data type except Image and Lob. 

Returns the value of result_expression matching the first when_expression with the value equal to input_expression, otherwise return the value of else_result_expression. 

The highest precedence data type from data types of all result_expression s and else_result_expression. 

Case when ([northwind].[dbo].[Orders].[EmployeeID] = 1) then 'less' when ([northwind].[dbo].[Orders].[EmployeeID] = 3 ) then 'mid' when ([northwind].[dbo].[Orders].[EmployeeID] = 4) then 'high' else 'not evaluated' end 

CASE..WHEN..THEN..ELSE..END 

CASE (input_expression)  
WHEN (when_expression)  
THEN (result_expression) […n]  
[ELSE (else_result_expression)] END 

Any data type except Image and Lob. 

Returns the value of result_expression matching the first when_expression with the value equal to input_expression, otherwise return the value of else_result_expression. 

The highest precedence data type from data types of all result_expression s and else_result_expression. 

CASE 'USA' WHEN [Retail].[dbo].[Orders].[ShipCountry] THEN 1 else [Retail].[dbo].[Orders].[OrderID] END 

CASE WHEN...THEN...ELSE...END

CASE WHEN (when_expression)  
THEN (result_expression) […n]  
[ELSE (else_result_expression)] END 

Any data type except Image and Lob. 

Returns the value of result_expression matching the first when_expression with the value equal to input_expression, otherwise return the value of else_result_expression. 

The highest precedence data type from data types of all result_expressions and else_result_expression. 

Case when ([northwind].[dbo].[Orders].[EmployeeID] = 1) then 'less' when ([northwind].[dbo].[Orders].[EmployeeID] = 3 ) then 'mid' when ([northwind].[dbo].[Orders].[EmployeeID] = 4) then 'high' else 'not evaluated' end 

RUNNINGSUM 

RUNNINGSUM(expression) 

Numeric, Money. 

Returns the sum of all the values of expression from the first row up to the current row. 

The same data type as expression. 

RUNNINGSUM([Retail].[dbo].[Orders].[Freight]) 

RUNNINGAVG 

RUNNINGAVG(expression) 

Numeric, Money. 

Returns the average of all the values of expression from the first row up to the current row. 

The same data type as expression. 

RUNNINGAVG([Retail].[dbo].[Orders].[Freight]) 

RUNNINGCOUNT 

RUNNINGCOUNT(expression) 

Any data type except Image and Lob. 

Returns the number of unique values of expression from the first row up to the current row. 

Numeric. 

RUNNINGCOUNT([Retail].[dbo].[Orders].[OrderID]) 

Datepart 

Abbreviations 

year 

yy, yyyy 

quarter 

qq, q 

month 

mm, m 

dayofyear 

dy, y 

day 

dd, d 

week 

ww, wk 

weekday 

dw 

hour 

hh 

minute 

mi, n 

second 

ss, s 

millisecond 

ms