Versions Compared

Old Version 9

changes.mady.by.user Geert Graat

Saved on

compared with

New Version 10

changes.mady.by.user Geert Graat

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

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 and SECONDS.
  • 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
typenote
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
typenote

The order in which the dates are placed is irrelevant. YEARS BETWEEN date1 AND date2 = YEARS BETWEEN date2 AND date1

Return type

  • integer
UI Text Box
typenote

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
Include Page
_nav_BackToTop
_nav_BackToTop

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, inclusive. The default working days are Monday, Tuesday, Wednesday, Thursday and Friday. This default can be overridden by supplying a list with alternative working days.

Valid types for period are:

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

  • WEEKS

  • MONTHS

  • YEARS

Syntax

Code Block
WORKINGDAYS BETWEEN date1 AND date2WORKINGDAYS_BETWEEN ( date1, date2, 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 DAYOFWEEK function, e.g. Sunday = 1, Monday = 2 etc.

UI Text Box
typenote

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
typenote

The order in which the dates are placed is irrelevant. YEARS BETWEEN date1 AND date2 = YEARS BETWEEN date2 AND date1

Return type

  • integer

UI Text Box
typenote

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

 

Include Page
_nav_BackToTop
_nav_BackToTop

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

Include Page
_nav_BackToTop
_nav_BackToTop

...

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

Include Page
_nav_BackToTop
_nav_BackToTop

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

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

Include Page
_nav_BackToTop
_nav_BackToTop

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
Code Block
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 ) ) = 5
  • DAYOFWEEK ( DATETIME ( 2015 , 12 , 28 , 0 , 0 , 0 ) ) = 2
  • DAYOFWEEK ( DATE ( 2016 , 01 , 02 ) ) = 7
  • DAYOFWEEK ( ? ) = ?

Include Page
_nav_BackToTop
_nav_BackToTop