Page History

...

Category	Function	Description
Logic	AND	Returns TRUE if all of its arguments are TRUE
OR	Returns TRUE if any argument is TRUE
TRUE	Returns the boolean value TRUE
FALSE	Returns the boolean value FALSE
NOT	Reverses the boolean value of its argument
UNKNOWN	Returns the logical value unknown
Text	CONCATENATE	Joins several text items into one text item
JOIN	Joins several text items into one text item, while placing a separator character between them
MATCH	Checks if a string completely matches a regular expression
FIND	Returns found characters if a pattern is found within text value
FIRST	Returns the first position where one text value is found within another
LAST	Returns the last position where one text value is found within another
Statistics	AVERAGE	Returns the average of its arguments
MIN	Returns the minimum of a specified value in a list or collection
MAX	Returns the maximum of a specified value in a list or collection
COUNT	Counts the number of instances in a specified collection
Date and Time	DATETIME	Converts a date in the form of text or a date object to a dateTime object
DATE	Converts a date in the form of text or a dateTime object to a date object
ADD PERIOD TO DATE	Adds a specified period (day, month etc.) to a date
ADD PERIOD TO DATETIME	Adds a specified period (day, month etc.) to a date
SUBTRACT PERIOD FROM DATE	Subtracts a specified period (day, month etc.) from a date and time
SUBTRACT PERIOD FROM DATETIME	Subtracts a specified period (day, month etc.) from a date and time
PERIOD BETWEEN	Returns the period (days, months etc.) between two dates, two dateTimes or a date-dateTime combination
YEAR	Returns the number of the year
MONTH	Returns the number of the month
DAY	Returns the number of the day in the month
HOUR	Returns the number of hours in a dateTime object
MINUTE	Returns the number of minutes in a dateTime object
SECOND	Returns the number of seconds in a dateTime object
TODAY	Returns today’s date
NOW	Returns the current date and time
Collection	SIZE collection	Returns the size of a collection
UNIQUE collection	Filters duplicate instances in a collection
SUBSET OF	Returns a subset of a collection
UNION	Combines two collections into one new collection
INTERSECTION	Returns the intersection of two collections
Math	ADD	Adds two numbers
SUM	Sums the specified values in a collection
SUBTRACT	Subtracts two numbers
DIVIDE	Divides two numbers
DIV	Returns the integer portion of a division
MOD	Returns the remainder from division
ROUND	Rounds a number to a specified number of digits
ROUND TO MULTIPLE	Rounds a number to the nearest integer or to the nearest multiple of significance
MULTIPLY	Multiplies two numbers
PRODUCT	Multiplies the specified values in a collection
POWER	Returns the result of a number raised to a power
LESS	Compares two number values and returns TRUE when one is smaller than the other
LESS OR EQUAL	Compares two number values and returns TRUE when one is equal or smaller than the other
GREATER	Compares two number values and returns TRUE when one is greater than the other
GREATER OR EQUAL	Compares two number values and returns TRUE when one is equal to or greater than the other
EQUAL	Compares two values and returns TRUE when they are equal
NOT EQUAL	Compares two number values and returns TRUE when they are not equal
List	EXISTS	Returns TRUE if there is an instance that matches the specifications
ALL	Returns a collection of all instances of a specified entity
COLLECT FROM	Returns a collection for a specified entity
COLLECT FROM WHERE	Returns a collection for a specified entity that matches the specified conditions
COLLECT FROM NAMED	Returns a collection for a specified entity
COLLECT FROM NAMED WHERE	Returns a collection for a specified entity that matches the specified conditions
UNPACK	Returns the content of a single-valued list
Conversion	TEXT	Converts a value type to string
INTEGER	Converts a value type to integer
NUMBER	Converts a value type to number
CURRENCY	Converts a value type to currency
PERCENTAGE	Converts a value type to percentage
DATE	Converts a value type to date
DATETIME	Converts a value type to datetime
BOOLEAN	Converts a value type to boolean
More functions	ISUNKNOWN	Replaces an unknown value with a specified value
IS A
TYPE OF
AUTHORIZATIONROLES
CHANNEL
LANGUAGE
PAGE
PRIVILEGES
EVENT

Date and Time

DATETIME

You can use this function to create²⁾ a dateTime value out of integers or numbers ³⁾.

Syntax

DATETIME(year, month, day, hour, minute, second)

year - An integer value between and including 1 and 9999 that represents the year in the constructed date.
month - An integer value between and including 1 and 12 that represents the month in the constructed date.
day - An integer value between and including 1 and 31 that represents the day in the constructed date.
hour - An integer value between and including 0 and 23 that represents the hours in the constructed time.
minute - An integer value between and including 0 and 59 that represents the minutes in the constructed time.
second - An integer value between and including 0 and 59 that represents the seconds in the constructed time.

Return type

dateTime

Examples

DATETIME(2010, 5, 25, 23, 00, 00) returns 25-5-2010 23:00:00
DATETIME(-2010, 5, 25, 23, 00, 00) results in UNKNOWN
DATETIME(2010, 5, 25, 45, 00, 00) results in UNKNOWN

^1), 2) there is also a conversion function DATETIME.

³⁾ Numbers will be rounded to integers.

DATE

You can use this function to create¹⁾ a date value out of integers or numbers²⁾.

Syntax

DATE(year, month, day)

year - An integer value between and including 1 and 9999 that represents the year in the constructed date.
month - An integer value between and including 1 and 12 that represents the month in the constructed date.
day - An integer value between and including 1 and 31 that represents the day in the constructed date.

Return type

date

Examples

DATE(2010, 5, 25) returns 25-5-2010
DATE(-2010, 5, 25) results in UNKNOWN
DATE(2010, 2, 29) results in an error

¹⁾ there is also a conversion function DATE.

²⁾ Numbers will be rounded to integers.

ADD PERIOD TO DATE

This function calculates a new date by adding a specified period to a specified date.

Syntax

date + number period

date - The date that you want to add the specified period to.
number - Specifies how may periods you want to add to the date.
period - A keyword that specifies which time unit to add. Valid units are DAYS, WEEKS, MONTHS and YEARS.

Return type

date

Examples

01-01-2015 + 1 DAYS equals 02-01-2015
01-01-2015 + 1 WEEKS equals 08-01-2015
01-01-2015 + 1 MONTHS equals 01-02-2015
01-01-2015 + 1 YEARS equals 01-01-2015

ADD PERIOD TO DATETIME

This function calculates a new dateTime by adding a specified period to a specified dateTime.

Syntax

dateTime + number period

dateTime - The dateTime that you want to add the specified period to.
number - Specifies how may periods you want to add to the dateTime.
period - A keyword that specifies which time unit to add. Valid units are DAYS, WEEKS, MONTHS, YEARS, HOURS, MINUTES and SECONDS.

Return type

dateTime

Examples

01-01-2015 13:23:04 + 1 DAYS equals 02-01-2015 13:23:04
01-01-2015 13:23:04 + 25 HOURS equals 02-01-2015 14:23:04
01-01-2015 13:23:04 + 60 SECONDS equals 01-01-2015 13:24:04
01-01-2015 13:23:04 + 1 MINUTES equals 01-01-2015 13:24:04

SUBTRACT PERIOD FROM DATE

This function calculates a new date by subtracting a specified period from a specified date.

Syntax

date – number period

date - The date that you want to subtract the specified period from.
number - Specifies how many periods you want to subtract from the date.
period - A keyword that specifies which time unit to subtract. Valid units are DAYS, WEEKS, MONTHS and YEARS.

Return type

date

Examples

01-01-2015 – 1 DAYS equals 31-12-2014
01-01-2015 – 1 WEEKS equals 25-12-2014
01-01-2015 – 1 MONTHS equals 01-12-2014
01-01-2015 – 1 YEARS equals 01-01-2014

SUBTRACT PERIOD FROM DATETIME

This function calculates a new dateTime by subtracting a specified period from a specified dateTime.

Syntax

dateTime – number period

dateTime - The dateTime that you want to subtract the specified period from.
number - Specifies how many periods you want to subtract from the dateTime.
period - A keyword that specifies which time unit to subtract. Valid units are DAYS, WEEKS, MONTHS, YEARS, HOURS, MINUTES and SECONDS.

Return type

dateTime

Examples

01-01-2015 13:23:04 – 1 HOURS equals 01-01-2015 12:23:04
01-01-2015 13:23:04 – 25 HOURS equals 31-12-2014 12:23:04
01-01-2015 13:23:04 – 60 SECONDS equals 01-01-2015 13:22:04
01-01-2015 13:23:04 – 1 MINUTES equals 01-01-2015 13:22:04

PERIOD BETWEEN

This function calculates the period between two date or dateTime objects. This function returns the number of full periods of the specified type between two dates or dateTimes.

Syntax ¹⁾

period BETWEEN date1 AND date2

period ²⁾ - A keyword that specifies which time unit to return. Valid units are DAYS, WEEKS, MONTHS, YEARS, HOURS, MINUTES and SECONDS.
date1 - The date or dateTime value that represents the start date.
date2 - The date or dateTime value that represents the end date.

Return type

integer

Examples

DAYS BETWEEN DATE(2010, 1, 1) AND DATE(2010, 2, 1) equals 31
DAYS BETWEEN DATE(2010, 5, 31) AND DATE(2010, 6, 1) equals 1
DAYS BETWEEN DATE(2010, 5, 31) AND DATETIME(2010, 6, 1, 12, 0, 0) equals 1, since 1 day and 12 hours have elapsed
DAYS BETWEEN DATETIME(2010, 1, 1, 14, 0, 0) AND DATETIME(2010, 1, 2, 13, 0, 0) equals 0, for only 23 hours have elapsed

YEARS BETWEEN DATE(2009, 1, 1) AND DATE(2010, 1, 1) equals 1
YEARS BETWEEN DATE(2010, 1, 1) AND DATE(2020, 1, 1) equals 10
YEARS BETWEEN DATE(2010, 1, 1) AND DATE(2011, 5, 1) equals 1
YEARS BETWEEN DATETIME(2010, 1, 1, 12, 0, 0) AND DATE(2011,1,1) equals 0, since 364 days and 12 hours have elapsed

¹⁾ the order in which the dates are placed is irrelevant. YEARS BETWEEN date1 AND date2 = YEARS BETWEEN date2 AND date1

²⁾ you can use the periods HOURS, MINUTES and SECONDS only if at least one of the dates is a dateTime object or expression.

YEAR

YEAR returns the year of a date or dateTime value. The year is returned as a number in the range 1-9999.

Syntax

YEAR(date | dateTime)

date or dateTime - The date or dateTime value of which the year must be returned.

Return type

integer

Examples

YEAR(DATE(2015, 01, 01)) equals 2015
YEAR(DATE(1978, 04, 11)) equals 1978

MONTH

MONTH returns the month of a date or dateTime value. The month is returned as a number in the range 1-12.

Syntax

MONTH(date | dateTime)

date or dateTime - The date or dateTime value of which the month must be returned.

Return type

integer

Examples

MONTH(DATE(2015, 01, 01)) equals 1
MONTH(DATE(1978, 04, 11)) equals 4

DAY

DAY returns the day of a date or dateTime value. The day is returned as a number in the range 1-31.

Syntax

DAY(date | dateTime)

date or dateTime - The date or dateTime value of which the day must be returned.

Return type

integer

Examples

DAY(DATE(2015, 01, 01)) equals 1
DAY(DATE(1978, 04, 11)) equals 11

HOUR

HOUR returns the hour of a dateTime value. The hour is returned as a number in the range 0-23.

Syntax

HOUR(dateTime)

dateTime - The dateTime value of which the hour must be returned.

Return type

integer

Examples

HOUR(DATETIME(2015, 5, 25, 00, 00, 00)) equals 0
HOUR(DATETIME(2015, 5, 25, 23, 00, 00)) equals 23

MINUTE

MINUTE returns the minute of a dateTime value. The minute is returned as a number in the range 0-59.

Syntax

MINUTE(dateTime)

dateTime - The dateTime value of which the minute must be returned.

Return type

integer

Examples

MINUTE(DATETIME(2015, 5, 25, 00, 00, 00)) equals 0
MINUTE(DATETIME(2015, 5, 25, 23, 45, 00)) equals 45

SECOND

SECOND returns the second of a dateTime value. The second is returned as a number in the range 0-59.

Syntax

SECOND(dateTime)

dateTime - The dateTime value of which the second must be returned.

Return type

integer

Examples

SECOND(DATETIME(2015, 5, 25, 00, 00, 00)) equals 0
SECOND(DATETIME(2015, 5, 25, 23, 45, 59)) equals 59

TODAY

This function returns the current date of the server. The date is given as base type date, formatted as dd-mm-yyyy.

Syntax

TODAY

Return type ¹⁾

date

¹⁾ the TODAY function returns a date stamp. It is only recalculated when the expression in which you use the TODAY function is reevaluated. This recalculation will not cause other expressions that use the TODAY function to be reevaluated.

NOW

This function returns the current date and time of the server. The date and time are given as base type dateTime, formatted as dd-mm-yyyy HH:mm:ss.

Syntax

NOW

Return type ¹⁾

dateTime

¹⁾ the NOW function returns a date- and timestamp. It is only recalculated when the expression in which you use the NOW function is reevaluated. This recalculation will not cause other expressions that use the NOW function to be reevaluated.

Collection

SIZE collection

This function determines the size of a collection.

Syntax

SIZE(collection)

collection - A collection of attribute or entity instances.

Return type

integer

Example

Suppose you created a Parent and a Child entity. Parent has a multivalued relation with Child via the relation Parent.has_Children.

If the following instances where created:

Parent instance	Child instance	Child.name	Child.hobbies
Parent_1	Child_1	“Kim”	“Reading”, “Dancing”
Child_2	“Rick”	“Tennis”, “Dancing”
Child_3	“Bob”	“Painting”, “Basketball”, “Reading”
Parent_2	Child_4	“Mary”	“Football”

Then:

without selecting a parent SIZE(Parent.has_children) results in an error
for Parent_1 SIZE(Parent.has_children) results in 3
for Parent_2 SIZE(Parent.has_children) results in 1
for Child_1 SIZE(Child.hobbies) results in 2
for Child_3 SIZE(Child.hobbies) results in 3
for Child_4 SIZE(Child.hobbies) results in 1
SIZE(? (= UNKNOWN)) results in 0¹⁾

¹⁾ COUNT and SIZE are similar except for ?: COUNT(?) = UNKNOWN

...

Space shortcuts

Page tree

Versions Compared

Old Version 5

New Version 6

Key

Date and Time

DATETIME

DATE

ADD PERIOD TO DATE

ADD PERIOD TO DATETIME

SUBTRACT PERIOD FROM DATE

SUBTRACT PERIOD FROM DATETIME

PERIOD BETWEEN

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

TODAY

NOW

Collection

SIZE collection