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 | ||
---|---|---|
| ||
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 | ||
---|---|---|
| ||
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. |
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 | ||
---|---|---|
|
DATETIME
You can use this function to create a dateTime value out of integers or numbers.
UI Text Box | ||
---|---|---|
| ||
Numbers will be rounded to integers. |
UI Text Box | ||
---|---|---|
| ||
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 | ||
---|---|---|
| ||
Numbers will be rounded to integers. |
UI Text Box | ||
---|---|---|
| ||
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 |
...
Syntax
Code Block |
---|
date + number period |
...
specified |
...
date |
...
. |
...
UI Text Box | ||
---|---|---|
| ||
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
...
Calculates a new dateTime by adding a specified period to a |
...
Syntax
Code Block |
---|
dateTime + number period |
...
specified |
...
dateTime. |
...
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
...
...
Calculates a new date by subtracting a specified period from a |
...
Syntax
Code Block |
---|
date - number period |
...
specified |
...
date |
...
. |
...
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
...
...
...
Calculates a new dateTime by subtracting a specified period from a specified dateTime. |
Syntax
Code Block |
---|
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
andSECONDS
.
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
andSECONDS
. - 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 | ||
---|---|---|
| ||
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 | ||
---|---|---|
| ||
The order in which the dates are placed is irrelevant. |
Return type
- integer
...
type | note |
---|
...
. |
...
Examples
...
...
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 elapsedYEARS 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. |
...
Valid types for period are:
...
SECONDS
...
MINUTES
...
HOURS
...
DAYS
...
WEEKS
...
MONTHS
...
Syntax
...
...
date1 - The date or dateTime value that represents the start date.
date2 - The date or dateTime value that represents the end date.
workingDays - A list containing alternative working days. Note that the days should be specified as returned by the DAYOFWEEK function, e.g. Sunday = 1, Monday = 2 etc.
UI Text Box | ||
---|---|---|
| ||
You can use the periods |
UI Text Box | ||
---|---|---|
| ||
The order in which the dates are placed is irrelevant. |
Return type
integer
UI Text Box | ||
---|---|---|
| ||
If you prefer a functional syntax, you can call these methods as: |
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 elapsedDAYS 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
...
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
DAY
returns the day of a date or dateTime value. The day is returned as a number in the range 1 - 31.
Syntax
Code Block |
---|
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 ) ) = 1
DAY ( DATE ( 1978 , 04 , 11 ) ) = 11
...
HOUR
HOUR
returns the hour of a dateTime value. The hour is returned as a number in the range 0 - 23.
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
SECOND
returns the second of a dateTime value. The second is returned as a number in the range 0 - 59.
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
This function returns the current date of the server. The date is given as base type date, formatted as dd-mm-yyyy.
Syntax
Code Block |
---|
TODAY |
Return type
- date
UI Text Box | ||
---|---|---|
| ||
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
Code Block |
---|
NOW |
Return type
- dateTime
UI Text Box | ||
---|---|---|
| ||
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
...
Alternative notation for WORKINGDAYS BETWEEN. | |
Returns the year of a date or dateTime value. | |
MONTH | Returns the month of a date or dateTime value. |
DAY | Returns the day of a date or dateTime value. |
HOUR | Returns the hour of a dateTime value. |
MINUTE | Returns the minute of a dateTime value. |
SECOND | Returns the second of a dateTime value. |
TODAY | Returns the current date of the server. |
NOW | Returns the current date and time of the server. |
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 | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
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 ) ) = 5DAYOFWEEK ( DATETIME ( 2015 , 12 , 28 , 0 , 0 , 0 ) ) = 2
DAYOFWEEK
( DATE ( 2016 , 01 , 02 ) ) = 7DAYOFWEEK
( ? ) = ?
...