You are viewing the documentation for Blueriq 17. Documentation for other versions is available in our documentation directory.
PERIOD BETWEEN
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
Inputs
- 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. So possible combinations are: YEARS BETWEEN, MONTHS BETWEEN, WEEKS BETWEEN, DAYS BETWEEN, HOURS BETWEEN, MINUTES BETWEEN and SECONDS BETWEEN
The order in which the dates are placed is relevant. YEARS BETWEEN date1 AND date2 = 0 - 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:
YEARS_BETWEEN(TODAY, UserInput.Date)MONTHS_BETWEEN(TODAY, UserInput.Date)WEEKS_BETWEEN(TODAY, UserInput.Date)DAYS_BETWEEN(TODAY, UserInput.Date)HOURS_BETWEEN(NOW, UserInput.DateTime)MINUTES_BETWEEN(NOW, UserInput.DateTime)SECONDS_BETWEEN(NOW, UserInput.DateTime)
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 |
Overview
Content Tools