You are viewing the documentation for Blueriq 17. Documentation for other versions is available in our documentation directory.

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. 

Overview

FunctionDescription
DATETIMECreates a dateTime value out of integers.
DATECreates a date value out of integers.
ADD PERIOD TO DATECalculates a new date by adding a specified period to a specified date.
ADD PERIOD TO DATETIMECalculates a new dateTime by adding a specified period to a specified dateTime.
SUBTRACT PERIOD FROM DATECalculates a new date by subtracting a specified period from a specified date.
SUBTRACT PERIOD FROM DATETIMECalculates a new dateTime by subtracting a specified period from a specified dateTime.
PERIOD BETWEENCalculates the period between two date or dateTime objects.

WORKINGDAYS BETWEEN

Calculates the working days between two date or dateTime objects.
WORKINGDAYS_BETWEENAlternative notation for WORKINGDAYS BETWEEN. 

YEAR

Returns the year of a date or dateTime value.
MONTHReturns the month of a date or dateTime value.
DAYReturns the day of a date or dateTime value.
HOURReturns the hour of a dateTime value.
MINUTEReturns the minute of a dateTime value.
SECONDReturns the second of a dateTime value.
TODAYReturns the current date of the server.
NOWReturns the current date and time of the server.
WEEKNUMBERReturns the week number within the year of a given date or dateTime value.
DAYOFWEEKReturns the number of the day within the week of a given date or dateTime value. 

Functions

DATETIME


Creates a dateTime value out of integers.

Numbers will be rounded to integers.

There is also a conversion function DATETIME.


Syntax

DATETIME ( year , month , day , hour , minute , second )

Inputs
  • 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

ExpressionResultType
DATETIME ( 2010 , 5 , 25 , 23 , 00 , 00 )25-5-2010 23:00:00DateTime
DATETIME ( -2010 , 5 , 25 , 23 , 00 , 00 )UNKNOWNDateTime
DATETIME ( 2010 , 5 , 25 , 45 , 00 , 00 )UNKNOWNDateTime

Back to Top


DATE


Creates a date value out of integers.

Numbers will be rounded to integers.

There is also a conversion function DATE.


Syntax

 DATE ( year , month , day )


Inputs
  • 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

ExpressionResultType
DATE ( 2010 , 5 , 25 )25-5-2010Date
DATE ( -2010 , 5 , 25 )UNKNOWNDate
DATE ( 2010 , 2 , 29 )Error


Back to Top


ADD PERIOD TO DATE


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


Syntax

date + number period


Inputs
  • 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(02-01-2015 , 1 ) = 02-01-2015


Return type

  • date


Examples

ExpressionAlternative syntaxResultType
DATE("2015-01-01") + 1 DAYSINCREMENT_DAYS( DATE("2015-01-01")  , 1 )2015-01-02Date
DATE("2015-01-01") + 1 WEEKSINCREMENT_WEEKS( DATE("2015-01-01")  , 1 )2015-01-08Date
DATE("2015-01-01") + 1 MONTHSINCREMENT_MONTHS( DATE("2015-01-01")  , 1 )2015-02-01Date
DATE("2015-01-01") + 1 YEARSINCREMENT_YEARS( DATE("2015-01-01")  , 1 )

2016-01-01

Date


Back to Top


ADD PERIOD TO DATETIME


Calculates a new dateTime by adding a specified period to a specified dateTime.


Syntax

dateTime + number period


Inputs
  • 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

ExpressionResultType
DATETIME("2015-01-01 13:23:04") + 1 DAYS2015-01-02 13:23:04DateTime
DATETIME("2015-01-01 13:23:04") + 25 HOURS2015-01-02 14:23:04DateTime
DATETIME("2015-01-01 13:23:04") + 60 SECONDS2015-01-01 13:24:04DateTime
DATETIME("2015-01-01 13:23:04") + 1 MINUTES2015-01-01 13:24:04DateTime


Back to Top




SUBTRACT PERIOD FROM DATE


Calculates a new date by subtracting a specified period from a specified date.


Syntax

date - number period



Inputs
  • 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

ExpressionResultType
01-01-2015 – 1 DAYS31-12-2014Date
01-01-2015 – 1 WEEKS25-12-2014Date
01-01-2015 – 1 MONTHS01-12-2014Date
01-01-2015 – 1 YEARS01-01-2014Date



Back to Top




SUBTRACT PERIOD FROM DATETIME


Calculates a new dateTime by subtracting a specified period from a specified dateTime.


Syntax

dateTime - number period



Inputs
  • 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

ExpressionResultType
01-01-2015 13:23:04 – 1 HOURS01-01-2015 12:23:04DateTime
01-01-2015 13:23:04 – 25 HOURS

31-12-2014 12:23:04

DateTime
01-01-2015 13:23:04 – 60 SECONDS01-01-2015 13:22:04DateTime
01-01-2015 13:23:04 – 1 MINUTES01-01-2015 13:22:04DateTime


Back to Top




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, 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.

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

ExpressionResultTypeNote
DAYS BETWEEN DATE( 2010 , 1 , 1 ) AND DATE( 2010 , 2 , 1 )31Integer
DAYS_BETWEEN ( DATE ( 2010 , 1 , 1 ) , DATE ( 2010 , 2 , 1 ) )31Integer
DAYS BETWEEN DATE ( 2010 , 5 , 31 ) AND DATE ( 2010 , 6 , 1 )1Integer
DAYS BETWEEN DATE( 2010 , 5 , 31 ) AND DATETIME ( 2010 , 6 , 1 , 12 , 0 , 0 )1IntegerTime is ignored
DAYS BETWEEN DATETIME ( 2010 , 1 , 1 , 14 , 0 , 0 ) AND DATETIME ( 2010 , 1 , 2 , 13 , 0 , 0 )1IntegerTime is ignored
YEARS BETWEEN DATE ( 2009 , 1 , 1 ) AND DATE ( 2010 , 1 , 1 )1Integer
YEARS BETWEEN DATE ( 2010 , 1 , 1 ) AND DATE ( 2020 , 1 , 1 )10Integer
YEARS BETWEEN DATE ( 2010 , 1 , 1 ) AND DATE ( 2011 , 5 , 1 )1Integer
YEARS BETWEEN DATETIME ( 2010 , 1 , 1 , 12 , 0 , 0 ) AND DATE ( 2011 , 1 , 1 )1IntegerTime is ignored



Back to Top


WORKINGDAYS BETWEEN

WORKINGDAYS_BETWEEN


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 )



Inputs

  • 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.

The order in which the dates are placed is irrelevant. WORKINGDAYS_BETWEEN date1 AND date2 = WORKINDAYS_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

ExpressionResultTypeNote
WORKINGDAYS BETWEEN DATE( 2015 , 1 , 1 ) AND DATE( 2015 , 2 , 1 )22Integer
WORKINGDAYS_BETWEEN ( DATE ( 2015 , 1 , 1 ) , DATE ( 2015 , 2 , 1 ) , [ 3, 4, 5, 6, 7 ] )23IntegerWork week Tuesday - Saturday
WORKINGDAYS_BETWEEN ( DATE ( 2015 , 1 , 1 ) , DATE ( 2015 , 2 , 1 ) , [ 2, 3, 4, 5 ] )17Integer4 day work week Monday - Thursday

WORKINGDAYS BETWEEN DATE ( 2015 , 6 , 1 )

AND DATETIME ( 2015 , 6 , 2 , 12 , 0 , 0 )

1IntegerTime is ignored

WORKINGDAYS BETWEEN DATETIME ( 2015 , 5 , 29, 12 , 0 , 0 )

AND DATE ( 2015 , 5 , 31 )

1Integer

Time is ignored

30-5 and 31-5 are Saturday and Sunday

Back to Top


YEAR


Returns the year of a date or dateTime value. The year is returned as an integer in the range 1 - 9999.


Syntax

YEAR ( date | dateTime )



Inputs
  • date or dateTime - The date or dateTime value of which the year must be returned.


Return type

  • integer


Examples

ExpressionResultType
YEAR ( DATE ( 2015 , 01 , 01 ) )2015Integer
YEAR ( DATE ( 1978 , 04 , 11 ) )1978Integer


Back to Top




MONTH


Returns the month of a date or dateTime value. The month is returned as an integer in the range 1 - 12.


Syntax

MONTH ( date | dateTime )


Inputs
  • date or dateTime - The date or dateTime value of which the month must be returned.


Return type

  • integer


Examples

ExpressionResultType
MONTH ( DATE ( 2015 , 01 , 01 ) )1Integer
MONTH ( DATE ( 1978 , 04 , 11 ) )4Integer


 

Back to Top




DAY


Returns the day of a date or dateTime value. The day is returned as an integer in the range 1 - 31.


Syntax

DAY ( date | dateTime )


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


Return type

  • integer


Examples

ExpressionResultType
DAY ( DATE ( 2015 , 01 , 01 ) )1Integer
DAY ( DATE ( 1978 , 04 , 11 ) )11Integer


Back to Top


HOUR


Returns the hour of a dateTime value. The hour is returned as an integer in the range 0 - 23.


Syntax

HOUR ( dateTime )


Inputs
  • dateTime - The dateTime value of which the hour must be returned.


Return type

  • integer


Examples

ExpressionResultType
HOUR ( DATETIME ( 2015 , 5 , 25 , 00 , 00 , 00 ) )0Integer
HOUR ( DATETIME ( 2015 , 5 , 25 , 23 , 00 , 00 ) )23Integer


Back to Top


MINUTE


Returns the minute of a dateTime value. The minute is returned as an integer in the range 0 - 59.


Syntax

MINUTE ( dateTime )


Inputs
  • dateTime - The dateTime value of which the minute must be returned.


Return type

  • integer


Examples

ExpressionResultType
MINUTE ( DATETIME ( 2015 , 5 , 25 , 00 , 00 , 00 ) )0Integer
MINUTE ( DATETIME ( 2015 , 5 , 25 , 23 , 45 , 00 ) )45Integer


Back to Top




SECOND


Returns the second of a dateTime value. The second is returned as an integer in the range 0 - 59.


Syntax

SECOND ( dateTime )


Inputs
  • dateTime - The dateTime value of which the second must be returned.


Return type

  • integer


Examples

ExpressionResultType
SECOND ( DATETIME ( 2015 , 5 , 25 , 00 , 00 , 00 ) )0Integer
SECOND ( DATETIME ( 2015 , 5 , 25 , 23 , 45 , 59 ) )59Integer


Back to Top




TODAY


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.

Back to Top




NOW


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.


Back to Top



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 4days 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 )


Inputs

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


Return type

  • integer


Examples

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


Back to Top


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 )


Inputs

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


Return type

  • integer



Examples


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


Back to Top


Didn't find the answer you were looking for? Try the advanced search!