Skip to main content

Izenda Functions

Izenda Functions

List of Built-in Functions

FunctionDescriptionResult Data TypesExamples
AVG

AVG(expression)

Numeric, Money		Returns the average of the values in a group. Null values are ignored.Numeric, MoneyAVG([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)

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)

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)

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(expressionASdata_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]ASTEXT)
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],'NoRegion')
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.CASEWHEN(BETWEEN([Retail].[dbo].[Orders].[EmployeeID],1,3))THEN1000else[Retail].[dbo].[Orders].[EmployeeID]END
AND

boolean_expressionANDboolean_expression

Boolean.		Returns TRUE when both expressions are TRUE, otherwise returns FALSE.Boolean.CASEWHEN([Retail].[dbo].[Orders].[EmployeeID]=1AND[Retail].[dbo].[Orders].[CustomerID]='DELDG')THEN1000else[Retail].[dbo].[Orders].[EmployeeID]end
OR

boolean_expressionANDboolean_expression

Boolean.		Returns TRUE when either expression is TRUE, otherwise returns FALSE.Boolean.CASEWHEN([Retail].[dbo].[Orders].[EmployeeID]=1OR[Retail].[dbo].[Orders].[EmployeeID]=2)THEN1000else[Retail].[dbo].[Orders].[EmployeeID]end
DISTINCT

DISTINCT(expression) or DISTINCTexpression

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

CASEWHEN(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.Casewhen([northwind].[dbo].[Orders].[EmployeeID]=1)then'less'when([northwind].[dbo].[Orders].[EmployeeID]=3)then'mid'when([northwind].[dbo].[Orders].[EmployeeID]=4)  then'high'else'notevaluated'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]THEN1else[Retail].[dbo].[Orders].[OrderID]END
**CASE WHEN…THEN…ELSE…END

CASEWHEN(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.Casewhen([northwind].[dbo].[Orders].[EmployeeID]=1)then'less'when([northwind].[dbo].[Orders].[EmployeeID]=3)then'mid'when([northwind].[dbo].[Orders].[EmployeeID]=4)  then'high'else'notevaluated'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])

List of Dateparts and Abbreviations

DatepartAbbreviations
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekww, wk
weekdaydw
hourhh
minutemi, n
secondss, s
millisecondms

Related articles