You are viewing the documentation for Blueriq 17. Documentation for other versions is available in our documentation directory.
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) = 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.
Numbers will be rounded to integers.
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) = 25-5-2010
DATE(-2010, 5, 25) = UNKNOWN
DATE(2010, 2, 29) results in an 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
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
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 = 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
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 = 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
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
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
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, 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.
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
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
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)) = 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
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
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
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
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
TODAY
Return type
- date
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
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 the first day of the week that contains at least x days from that year. The first day of the week and the minimal days in that first week are defined by the region settings. In most cases the first day of the week is Monday and the minimal days in the first week is 4 (these are the values reflecting ISO 8601 and many national standards).
For example, January 1, 1998 was a Thursday. Then week 1 of 1998 starts on December 29, 1997, and ends on January 4, 1998. If, however, the first day of the week is defined as Sunday (as is the case in the US), then week 1 of 1998 starts on January 4, 1998, and ends on January 10, 1998; the first three days of 1998 then are part of week 53 of 1997.
Syntax
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 ( DATE ( 2014 , 12 , 29 ) ) = 1
WEEKNUMBER ( DATE ( 2015 , 12 , 28 ) ) = 53
WEEKNUMBER ( DATE ( 2016 , 01 , 02 ) ) = 53
WEEKNUMBER ( DATE ( 2016 , 01 , 04 ) ) = 1
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
Syntax
DAYOFWEEK( date | dateTime )
- date or dateTime - The date or dateTime value of which the week number must be returned.
Return type
- integer
Examples
DAYOFWEEK
( DATE ( 2015 , 01 , 01 ) ) = 5DAYOFWEEK ( DATE ( 2015 , 12 , 28 , 0 , 0 , 0 ) ) = 2
DAYOFWEEK
( DATE ( 2016 , 01 , 02 ) ) = 7DAYOFWEEK
( ? ) = ?