This article contains:

1. List of built in Functions

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') THEN1000 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]=1OR[Retail].[dbo].[Orders].[EmployeeID]=2)THEN1000 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 WHENN (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]) 

2. List of Dateparts and Abbreviations 

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