Page History

Learn more about the date and time functions DATETIME, DATE, SECONDS, SECONDS_BETWEEN, MINUTES, MINUTES_BETWEEN, HOURS, HOURS_BETWEEN, DAYS, DAYS_BETWEEN, WORKINGDAYS, WORKINGDAYS_BETWEEN, WEEKS, WEEKS_BETWEEN, MONTHS, MONTHS_BETWEEN, YEARS, YEARS_BETWEEN, BETWEEN, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TODAY, NOW, WEEKNUMBER, DAYOFWEEK.

Info

title	Calendar and timezones

Keep in mind that all dates are interpreted using the (proleptic) Gregorian calendar rules.

The timezone used in these type of functions is the system default timezone of the server that hosts the Blueriq runtime where the function is executed.

Info

title	Date and time formats

The way in which date and time values are displayed in the user interface is determined by the date display formats, which can be configured per language code.
For example the Dutch nl-NL date format could be configured as "dd-MM-yyyy", whereas the English en-US date format can be defined as "MM/dd/yyyy". More information about language specific formats can be found in Reference Guide: Language Formats

Overview

Function	Description
`DATETIME`	Creates a dateTime value out of integers.
`DATE`	Creates a date value out of integers.
`ADD PERIOD TO DATE`	Calculates

Table of Contents

minLevel	2

DATETIME

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

UI Text Box

type	note

Numbers will be rounded to integers.

UI Text Box

type	note

There is also a conversion function DATETIME.

Syntax

Code Block
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 ) = 25-5-2010 23:00:00
DATETIME ( -2010 , 5 , 25 , 23 , 00 , 00 ) = UNKNOWN
DATETIME ( 2010 , 5 , 25 , 45 , 00 , 00 ) = UNKNOWN

...

DATE

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

UI Text Box

type	note

Numbers will be rounded to integers.

UI Text Box

type	note

There is also a conversion function DATE.

Syntax

Code Block
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 ) = 25-5-2010
DATE ( -2010 , 5 , 25 ) = UNKNOWN
DATE ( 2010 , 2 , 29 ) results in an error

...

ADD PERIOD TO DATE

...

a new date by adding a specified period to a specified date.

Syntax

Code Block
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.

UI Text Box

type	note

If you prefer a functional syntax, you can use the INCREMENT_period functions. For example: INCREMENT_DAYS( 01-01-2015, 1) = 02-01-2015

Return type

date

Examples

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

ADD PERIOD TO DATETIME

...

Calculates a new dateTime by adding a specified period to a

...

Syntax

Code Block
dateTime + number period

...

specified

...

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 = 02-01-2015 13:23:04
01-01-2015 13:23:04 + 25 HOURS = 02-01-2015 14:23:04
01-01-2015 13:23:04 + 60 SECONDS = 01-01-2015 13:24:04
01-01-2015 13:23:04 + 1 MINUTES = 01-01-2015 13:24:04

SUBTRACT PERIOD FROM DATE

...

Calculates a new date by subtracting a specified period from a

...

Syntax

Code Block
date - number period

...

specified

...

Return type

date

Examples

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

...

date.

...

period - A keyword that specifies which time unit to subtract. Valid units are DAYS, WEEKS, MONTHS and YEARS.

SUBTRACT PERIOD FROM DATETIME

...

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 = 01-01-2015 12:23:04
01-01-2015 13:23:04 – 25 HOURS = 31-12-2014 12:23:04
01-01-2015 13:23:04 – 60 SECONDS = 01-01-2015 13:22:04
01-01-2015 13:23:04 – 1 MINUTES = 01-01-2015 13:22:04

PERIOD BETWEEN Calculates

...

PERIOD BETWEEN

...

the period between two date or dateTime objects

...

Valid types for period are:

SECONDS
MINUTES
HOURS
DAYS
WEEKS
MONTHS
YEARS

Syntax

Code Block
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.

UI Text Box

type	note

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

UI Text Box

type	note

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

Return type

integer

...

type	note

...

.

...

Examples

DAYS BETWEEN DATE( 2010 , 1 , 1 ) AND DATE( 2010 , 2 , 1 ) = 31
DAYS_BETWEEN ( DATE ( 2010 , 1 , 1 ) , DATE ( 2010 , 2 , 1 ) ) = 31
DAYS BETWEEN DATE ( 2010 , 5 , 31 ) AND DATE ( 2010 , 6 , 1 ) = 1
DAYS BETWEEN DATE( 2010 , 5 , 31 ) AND DATETIME ( 2010 , 6 , 1 , 12 , 0 , 0 ) = 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 ) = 0, for only 23 hours have elapsed

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

...

WORKINGDAYS BETWEEN

...

Calculates the working days between two date or dateTime objects.

...

Syntax

...

WORKINGDAYS

...

_BETWEEN

...

date1 - The date or dateTime value that represents the start date.

...

date2 - The date or dateTime value that represents the end date.

	Alternative notation for WORKINGDAYS BETWEEN.
`YEAR`	Returns the year of a date or dateTime value.
`MONTH`	Returns the month of a date or dateTime value.
`DAY`	Returns

...

UI Text Box

type	note

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

Return type

integer

Examples

WORKINGDAYS BETWEEN DATE( 2015 , 1 , 1 ) AND DATE( 2015 , 2 , 1 ) = 22
WORKINGDAYS_BETWEEN ( DATE ( 2015 , 1 , 1 ) , DATE ( 2015 , 2 , 1 ) , [ 3, 4, 5, 6, 7 ] ) = 23 (work week Tuesday - Saterday)
WORKINGDAYS_BETWEEN ( DATE ( 2015 , 1 , 1 ) , DATE ( 2015 , 2 , 1 ) , [ 2, 3, 4, 5 ] ) = 17 (4 day work week Monday - Thursday)
WORKINGDAYS BETWEEN DATE ( 2015 , 6 , 1 ) AND DATETIME ( 2015 , 6 , 2 , 12 , 0 , 0 ) = 1, since 1 day and 12 hours have elapsed
WORKINGDAYS BETWEEN DATETIME ( 2015 , 5 , 29, 12 , 0 , 0 ) AND DATE ( 2015 , 5 , 31 ) = 0, since 12 working hours have elapsed

...

YEAR

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

Syntax

Code Block
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 ) ) = 2015
YEAR ( DATE ( 1978 , 04 , 11 ) ) = 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

Code Block
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 ) ) = 1
MONTH ( DATE ( 1978 , 04 , 11 ) ) = 4

...

DAY

...

the day of a date or dateTime value.

...

Syntax

...

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

Return type

integer

Examples

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

...

HOUR

...

HOUR Returns the hour of a dateTime value.

...

Syntax

Code Block
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 ) ) = 0
HOUR ( DATETIME ( 2015 , 5 , 25 , 23 , 00 , 00 ) ) = 23

...

MINUTE

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

Syntax

Code Block
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 ) ) = 0
MINUTE ( DATETIME ( 2015 , 5 , 25 , 23 , 45 , 00 ) ) = 45

...

SECOND

...

`MINUTE`	Returns the minute of a dateTime value.
`SECOND`	Returns the second of a dateTime value

...

.

...

Syntax

Code Block
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 ) ) = 0
SECOND ( DATETIME ( 2015 , 5 , 25 , 23 , 45 , 59 ) ) = 59

...

TODAY

...

Returns the current date of the server

...

.

...

Syntax

...

Return type

date

UI Text Box

type	note

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

...

NOW Returns the current date and time of the server.

...

Syntax

Code Block
NOW

Return type

dateTime

...

UI Text Box

type	note

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.

...

WEEKNUMBER

...

WEEKNUMBER Returns the week number within the year of a given date or dateTime value.

...


`DAYOFWEEK`	Returns the number of the day within the week of a given date or dateTime value.

Functions

Include Page

	Date and time function DATETIME
	Date and time function DATETIME

Include Page

	Date and time function DATE
	Date and time function DATE

Include Page

	Date and time function ADD PERIOD TO DATE
	Date and time function ADD PERIOD TO DATE

Include Page

	Date and time function ADD PERIOD TO DATETIME
	Date and time function ADD PERIOD TO DATETIME

Include Page

	Date and time function SUBTRACT PERIOD FROM DATE
	Date and time function SUBTRACT PERIOD FROM DATE

Include Page

	Date and time function SUBTRACT PERIOD FROM DATETIME
	Date and time function SUBTRACT PERIOD FROM DATETIME

Include Page

	Date and time function PERIOD BETWEEN
	Date and time function PERIOD BETWEEN

Include Page

	Date and time function WORKINGDAYS BETWEEN
	Date and time function WORKINGDAYS BETWEEN

Include Page

	Date and time function YEAR
	Date and time function YEAR

Include Page

	Date and time function MONTH
	Date and time function MONTH

Include Page

	Date and time function DAY
	Date and time function DAY

Include Page

	Date and time function HOUR
	Date and time function HOUR

Include Page

	Date and time function MINUTE
	Date and time function MINUTE

Include Page

	Date and time function SECOND
	Date and time function SECOND

Include Page

	Date and time function TODAY
	Date and time function TODAY

Include Page

	Date and time function NOW
	Date and time function NOW

Include Page

	Date and time function WEEKNUMBER
	Date and time function WEEKNUMBER

Include Page

	Date and time function DAYOFWEEK
	Date and time function DAYOFWEEK

For example, January 1, 2015 was a Thursday. Therefore week 1 of 2015 starts on December 29, 2014, and ends on January 4, 2015.

Syntax

Code Block
WEEKNUMBER ( date \| dateTime )

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

Return type

integer

Examples

WEEKNUMBER ( DATE ( 2015 , 01 , 01 ) ) = 1
WEEKNUMBER ( DATETIME ( 2014 , 12 , 28 , 0 , 0 , 0 ) ) = 1
WEEKNUMBER ( DATE ( 2015 , 12 , 28 ) ) = 53
WEEKNUMBER ( DATE ( 2016 , 01 , 02 ) ) = 53
WEEKNUMBER ( DATETIME ( 2016 , 01 , 04 , 0 , 0 , 0 ) ) = 1

...

DAYOFWEEK

...

Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7

...

Code Block
DAYOFWEEK( date \| dateTime )

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

...

integer

...

DAYOFWEEK ( DATE ( 2015 , 01 , 01 ) ) = 5
DAYOFWEEK ( DATETIME ( 2015 , 12 , 28 , 0 , 0 , 0 ) ) = 2
DAYOFWEEK ( DATE ( 2016 , 01 , 02 ) ) = 7
DAYOFWEEK ( ? ) = ?

...

Space shortcuts

Page tree

Versions Compared

Old Version 12

New Version Current

Key

Overview

DATETIME

DATE

ADD PERIOD TO DATE

PERIOD BETWEEN

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

NOW

WEEKNUMBER

Functions

DAYOFWEEK