You are viewing the documentation for Blueriq 17. Documentation for other versions is available in our documentation directory.
DATE
You can use this function to create a date value out of integers or numbers.
Numbers will be rounded to integers.
There is also a conversion function DATE.
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
| Expression | Result | Type |
|---|---|---|
| DATE ( 2010 , 5 , 25 ) | 25-5-2010 | Date |
| DATE ( -2010 , 5 , 25 ) | UNKNOWN | Date |
| DATE ( 2010 , 2 , 29 ) | Error |
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.
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
| Expression | Alternative syntax | Result | Type |
|---|---|---|---|
| 01-01-2015 + 1 DAYS | INCREMENT_DAYS( 01-01-2015 , 1 ) | 02-01-2015 | Date |
| 01-01-2015 + 1 WEEKS | INCREMENT_WEEKS( 01-01-2015 , 1 ) | 08-01-2015 | Date |
| 01-01-2015 + 1 MONTHS | INCREMENT_MONTHS( 01-01-2015 , 1 ) | 01-02-2015 | Date |
| 01-01-2015 + 1 YEARS | INCREMENT_YEARS( 01-01-2015 , 1 ) | 01-01-2016 | Date |
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
| Expression | Result | Type |
|---|---|---|
| 01-01-2015 13:23:04 + 1 DAYS | 02-01-2015 13:23:04 | DateTime |
| 01-01-2015 13:23:04 + 25 HOURS | 02-01-2015 14:23:04 | DateTime |
| 01-01-2015 13:23:04 + 60 SECONDS | 01-01-2015 13:24:04 | DateTime |
| 01-01-2015 13:23:04 + 1 MINUTES | 01-01-2015 13:24:04 | DateTime |
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
| Expression | Result | Type |
|---|---|---|
| 01-01-2015 – 1 DAYS | 31-12-2014 | Date |
| 01-01-2015 – 1 WEEKS | 25-12-2014 | Date |
| 01-01-2015 – 1 MONTHS | 01-12-2014 | Date |
| 01-01-2015 – 1 YEARS | 01-01-2014 | Date |
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, MINUTESandSECONDS.
Return type
- dateTime
Examples
| Expression | Result | Type |
|---|---|---|
| 01-01-2015 13:23:04 – 1 HOURS | 01-01-2015 12:23:04 | DateTime |
| 01-01-2015 13:23:04 – 25 HOURS | 31-12-2014 12:23:04 | DateTime |
| 01-01-2015 13:23:04 – 60 SECONDS | 01-01-2015 13:22:04 | DateTime |
| 01-01-2015 13:23:04 – 1 MINUTES | 01-01-2015 13:22:04 | DateTime |
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.
Valid types for period are:
- SECONDS
- MINUTES
- HOURS
- DAYS
- WEEKS
- MONTHS
- YEARS
Syntax
period BETWEEN date1 AND date2
- period - A keyword that specifies which time unit to return. Valid units are
DAYS,WEEKS,MONTHS,YEARS,HOURS,MINUTESandSECONDS. - date1 - The date or dateTime value that represents the start date.
- date2 - The date or dateTime value that represents the end date.
You can use the periods
HOURS, MINUTES and SECONDS only if at least one of the dates is a dateTime object or expression. On the other hand, with the periods DAYS, WEEKS, MONTHS and YEARS, the time component of a datetime value is ignored.
The order in which the dates are placed is irrelevant. YEARS BETWEEN date1 AND date2 = YEARS BETWEEN date2 AND date1
Return type
- integer
If you prefer a functional syntax, you can call these methods as: period_BETWEEN(date or datetime, date or datetime). For example: DAYS_BETWEEN(TODAY, UserInput.Date)
Examples
| Expression | Result | Type | Note |
|---|---|---|---|
| DAYS BETWEEN DATE( 2010 , 1 , 1 ) AND DATE( 2010 , 2 , 1 ) | 31 | Integer | |
| DAYS_BETWEEN ( DATE ( 2010 , 1 , 1 ) , DATE ( 2010 , 2 , 1 ) ) | 31 | Integer | |
| DAYS BETWEEN DATE ( 2010 , 5 , 31 ) AND DATE ( 2010 , 6 , 1 ) | 1 | Integer | |
| DAYS BETWEEN DATE( 2010 , 5 , 31 ) AND DATETIME ( 2010 , 6 , 1 , 12 , 0 , 0 ) | 1 | Integer | Time is ignored |
| DAYS BETWEEN DATETIME ( 2010 , 1 , 1 , 14 , 0 , 0 ) AND DATETIME ( 2010 , 1 , 2 , 13 , 0 , 0 ) | 1 | Integer | Time is ignored |
| YEARS BETWEEN DATE ( 2009 , 1 , 1 ) AND DATE ( 2010 , 1 , 1 ) | 1 | Integer | |
| YEARS BETWEEN DATE ( 2010 , 1 , 1 ) AND DATE ( 2020 , 1 , 1 ) | 10 | Integer | |
| YEARS BETWEEN DATE ( 2010 , 1 , 1 ) AND DATE ( 2011 , 5 , 1 ) | 1 | Integer | |
| YEARS BETWEEN DATETIME ( 2010 , 1 , 1 , 12 , 0 , 0 ) AND DATE ( 2011 , 1 , 1 ) | 1 | Integer | Time is ignored |
WORKINGDAYS BETWEEN
This function calculates the working days between two date or dateTime objects. It returns the number of full working days between two dates or dateTimes. The default working days are Monday, Tuesday, Wednesday, Thursday and Friday. This default can be overridden by supplying a list with alternative working days.
Syntax
WORKINGDAYS BETWEEN startDate AND endDate WORKINGDAYS_BETWEEN ( startDate, endDate, workingDays )
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 Date and time function: DATE function, e.g. Sunday = 1, Monday = 2 etc.
The order in which the dates are placed is irrelevant. YEARS BETWEEN date1 AND date2 = YEARS BETWEEN date2 AND date1. Also note that the time component of a datetime value is ignored, similar to the DAYS BETWEEN function.
Return type
integer
Examples
| Expression | Result | Type | Note |
|---|---|---|---|
| WORKINGDAYS BETWEEN DATE( 2015 , 1 , 1 ) AND DATE( 2015 , 2 , 1 ) | 22 | Integer | |
| WORKINGDAYS_BETWEEN ( DATE ( 2015 , 1 , 1 ) , DATE ( 2015 , 2 , 1 ) , [ 3, 4, 5, 6, 7 ] ) | 23 | Integer | Work week Tuesday - Saturday |
| WORKINGDAYS_BETWEEN ( DATE ( 2015 , 1 , 1 ) , DATE ( 2015 , 2 , 1 ) , [ 2, 3, 4, 5 ] ) | 17 | Integer | 4 day work week Monday - Thursday |
WORKINGDAYS BETWEEN DATE ( 2015 , 6 , 1 ) AND DATETIME ( 2015 , 6 , 2 , 12 , 0 , 0 ) | 1 | Integer | Time is ignored |
WORKINGDAYS BETWEEN DATETIME ( 2015 , 5 , 29, 12 , 0 , 0 ) AND DATE ( 2015 , 5 , 31 ) | 1 | Integer | Time is ignored 30-5 and 31-5 are Saturday and Sunday |
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
| Expression | Result | Type |
|---|---|---|
| YEAR ( DATE ( 2015 , 01 , 01 ) ) | 2015 | Integer |
| YEAR ( DATE ( 1978 , 04 , 11 ) ) | 1978 | Integer |
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
| Expression | Result | Type |
|---|---|---|
| MONTH ( DATE ( 2015 , 01 , 01 ) ) | 1 | Integer |
| MONTH ( DATE ( 1978 , 04 , 11 ) ) | 4 | Integer |
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
| Expression | Result | Type |
|---|---|---|
| DAY ( DATE ( 2015 , 01 , 01 ) ) | 1 | Integer |
| DAY ( DATE ( 1978 , 04 , 11 ) ) | 11 | Integer |
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
| Expression | Result | Type |
|---|---|---|
| HOUR ( DATETIME ( 2015 , 5 , 25 , 00 , 00 , 00 ) ) | 0 | Integer |
| HOUR ( DATETIME ( 2015 , 5 , 25 , 23 , 00 , 00 ) ) | 23 | Integer |
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
| Expression | Result | Type |
|---|---|---|
| MINUTE ( DATETIME ( 2015 , 5 , 25 , 00 , 00 , 00 ) ) | 0 | Integer |
| MINUTE ( DATETIME ( 2015 , 5 , 25 , 23 , 45 , 00 ) ) | 45 | Integer |
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
| Expression | Result | Type |
|---|---|---|
| SECOND ( DATETIME ( 2015 , 5 , 25 , 00 , 00 , 00 ) ) | 0 | Integer |
| SECOND ( DATETIME ( 2015 , 5 , 25 , 23 , 45 , 59 ) ) | 59 | Integer |
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.
WEEKNUMBER
WEEKNUMBER returns the week number within the year of a given date or dateTime value. The week number is returned as an integer in the range 1 - 53. Week 1 for a year is the earliest seven day period starting at Monday that contains at least 4 days from that year. This definition reflects the ISO 8601 Week dates definition.
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
WEEKNUMBER ( date | dateTime )
- date or dateTime - The date or dateTime value of which the week number must be returned.
Return type
- integer
Examples
| Expression | Result | Type |
|---|---|---|
| WEEKNUMBER ( DATE ( 2015 , 01 , 01 ) ) | 1 | Integer |
| WEEKNUMBER ( DATETIME ( 2014 , 12 , 28 , 0 , 0 , 0 ) ) | 1 | Integer |
| WEEKNUMBER ( DATE ( 2015 , 12 , 28 ) ) | 53 | Integer |
| WEEKNUMBER ( DATE ( 2016 , 01 , 02 ) ) | 53 | Integer |
| WEEKNUMBER ( DATETIME ( 2016 , 01 , 04 , 0 , 0 , 0 ) ) | 1 | Integer |
DAYOFWEEK
DAYOFWEEK returns the number of the day within the week of a given date or dateTime value, as follows:- Sunday = 1
- Monday = 2
- Tuesday = 3
- Wednesday = 4
- Thursday = 5
- Friday = 6
- Saturday = 7
DAYOFWEEK ( date | dateTime )
- date or dateTime - The date or dateTime value of which the week number must be returned.
- integer
Examples
| Expression | Result | Type |
|---|---|---|
| DAYOFWEEK ( DATE ( 2015 , 01 , 01 ) ) | 5 | Integer |
| DAYOFWEEK ( DATETIME ( 2015 , 12 , 28 , 0 , 0 , 0 ) ) | 2 | Integer |
| DAYOFWEEK ( DATE ( 2016 , 01 , 02 ) ) | 7 | Integer |
| DAYOFWEEK ( ? ) | UNKNOWN | Integer |
Overview
Content Tools