Versions Compared

Old Version 3

changes.mady.by.user Unknown User (a.jansen)

Saved on

compared with

New Version Current

changes.mady.by.user Tijmen Daatselaar

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
02
Panel
bgColorwhite

ADD PERIOD TO DATE


This function calculates Calculates a new date by adding a specified period to a specified date.


Syntax

Code Block
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.
Info
UI Text Box
typenote

If you prefer a functional syntax, you can use the INCREMENT_period functions. For example: INCREMENT_DAYS( 0102-01-2015 , 1 ) = 02-01-2015


Return type

  • date


Examples

ExpressionAlternative syntaxResultType
01-01-2015 + 1 DAYSINCREMENT_DAYS( 01-01-2015 , 1 )
DATE("2015-01-
2015Date01-01-2015
01") + 1
WEEKS
DAYSINCREMENT_
WEEKS( 01-01-2015 , 1 )08-01-2015Date01-01-2015 + 1 MONTHSINCREMENT_MONTHS( 01-01-2015 , 1 )01-02-2015Date01-01-2015 + 1 YEARSINCREMENT_YEARS( 01-01-2015 , 1 )

01-01-2016

Date

Back to top

Panel
bgColorwhite

ADD PERIOD TO DATETIME

This function calculates a new dateTime by adding a specified period to a specified dateTime.

Syntax

Code Block
dateTime + number period
InputsdateTime - 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

    ExpressionResultType01-01-2015 13:23:04 + 1 DAYS02-01-2015 13:23:04DateTime01-01-2015 13:23:04 + 25 HOURS02-01-2015 14:23:04DateTime01-01-2015 13:23:04 + 60 SECONDS01-01-2015 13:24:04DateTime01-01-2015 13:23:04 + 1 MINUTES01-01-2015 13:24:04DateTime

    Back to top

    Panel
    bgColorwhite

    SUBTRACT PERIOD FROM DATE

    This function calculates a new date by subtracting a specified period from a specified date.

    Syntax

    Code Block
    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

    ExpressionResultType01-01-2015 – 1 DAYS31-12-2014Date01-01-2015 – 1 WEEKS25-12-2014Date01-01-2015 – 1 MONTHS01-12-2014Date01-01-2015 – 1 YEARS01-01-2014Date
    Back to top
    Panel
    bgColorwhite

    SUBTRACT PERIOD FROM DATETIME

    This function calculates a new dateTime by subtracting a specified period from a specified dateTime.

    Syntax

    Code Block
    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

    ExpressionResultType01-01-2015 13:23:04 – 1 HOURS01-01-2015 12:23:04DateTime01-01-2015 13:23:04 – 25 HOURS

    31-12-2014 12:23:04

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

    Back to top

    Panel
    bgColorwhite

    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

    Code Block
    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.
    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. On the other hand, with the periods DAYS, WEEKS, MONTHS and YEARS, the time component of a datetime value is ignored.
    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

    ExpressionResultTypeNoteDAYS BETWEEN DATE( 2010 , 1 , 1 ) AND DATE( 2010 , 2 , 1 )31IntegerDAYS_BETWEEN ( DATE ( 2010 , 1 , 1 ) , DATE ( 2010 , 2 , 1 ) )31IntegerDAYS BETWEEN DATE ( 2010 , 5 , 31 ) AND DATE ( 2010 , 6 , 1 )1IntegerDAYS BETWEEN DATE( 2010 , 5 , 31 ) AND DATETIME ( 2010 , 6 , 1 , 12 , 0 , 0 )1IntegerTime is ignoredDAYS BETWEEN DATETIME ( 2010 , 1 , 1 , 14 , 0 , 0 ) AND DATETIME ( 2010 , 1 , 2 , 13 , 0 , 0 )1IntegerTime is ignoredYEARS BETWEEN DATE ( 2009 , 1 , 1 ) AND DATE ( 2010 , 1 , 1 )1IntegerYEARS BETWEEN DATE ( 2010 , 1 , 1 ) AND DATE ( 2020 , 1 , 1 )10IntegerYEARS BETWEEN DATE ( 2010 , 1 , 1 ) AND DATE ( 2011 , 5 , 1 )1IntegerYEARS BETWEEN DATETIME ( 2010 , 1 , 1 , 12 , 0 , 0 ) AND DATE ( 2011 , 1 , 1 )1IntegerTime is ignored
    Back to top
    Panel
    bgColorwhite

    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

    Code Block
    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 Date and time function: ADD PERIOD TO DATE function, e.g. Sunday = 1, Monday = 2 etc.
    UI Text Box
    typenote

    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

    ExpressionResultTypeNoteWORKINGDAYS BETWEEN DATE( 2015 , 1 , 1 ) AND DATE( 2015 , 2 , 1 )22IntegerWORKINGDAYS_BETWEEN ( DATE ( 2015 , 1 , 1 ) , DATE ( 2015 , 2 , 1 ) , [ 3, 4, 5, 6, 7 ] )23IntegerWork week Tuesday - SaturdayWORKINGDAYS_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
    Panel
    bgColorwhite

    YEAR

    This function 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 )
    Inputs
    • date or dateTime - The date or dateTime value of which the year must be returned.

    Return type

    • integer

    Examples

    ExpressionResultTypeYEAR ( DATE ( 2015 , 01 , 01 ) )2015IntegerYEAR ( DATE ( 1978 , 04 , 11 ) )1978Integer

    Back to top

    Panel
    bgColorwhite

    MONTH

    This function returns the month of a date or dateTime value. The month is returned as a number in the range 1 - 12.

    Syntax

    Code Block
    MONTH ( date | dateTime )
    Inputs
    • date or dateTime - The date or dateTime value of which the month must be returned.

    Return type

    • integer

    Examples

    ExpressionResultTypeMONTH ( DATE ( 2015 , 01 , 01 ) )1IntegerMONTH ( DATE ( 1978 , 04 , 11 ) ) 4Integer Include Page_nav_BackToTop_nav_BackToTop Panel
    bgColorwhite

    DAY

    This function returns the day of a date or dateTime value. The day is returned as a number in the range 1 - 31.

    Syntax

    Code Block
    DAY ( date | dateTime )
    Inputs
    • date or dateTime - The date or dateTime value of which the day must be returned.

    Return type

    • integer

    Examples

    ExpressionResultTypeDAY ( DATE ( 2015 , 01 , 01 ) )1IntegerDAY ( DATE ( 1978 , 04 , 11 ) )11Integer
    Back to top
    Panel
    bgColorwhite

    HOUR

    This function returns the hour of a dateTime value. The hour is returned as a number in the range 0 - 23.

    Syntax

    Code Block
    HOUR ( dateTime )
    Inputs
    • dateTime - The dateTime value of which the hour must be returned.

    Return type

    • integer

    Examples

    ExpressionResultTypeHOUR ( DATETIME ( 2015 , 5 , 25 , 00 , 00 , 00 ) )0IntegerHOUR ( DATETIME ( 2015 , 5 , 25 , 23 , 00 , 00 ) )23Integer

    Back to top

    Panel
    bgColorwhite

    MINUTE

    This function returns the minute of a dateTime value. The minute is returned as a number in the range 0 - 59.

    Syntax

    Code Block
    MINUTE ( dateTime )
    Inputs
    • dateTime - The dateTime value of which the minute must be returned.

    Return type

    • integer

    Examples

    ExpressionResultTypeMINUTE ( DATETIME ( 2015 , 5 , 25 , 00 , 00 , 00 ) )0IntegerMINUTE ( DATETIME ( 2015 , 5 , 25 , 23 , 45 , 00 ) )45Integer

    Back to top

    Panel
    bgColorwhite

    SECOND

    This function returns the second of a dateTime value. The second is returned as a number in the range 0 - 59.

    Syntax

    Code Block
    SECOND ( dateTime )
    Inputs
    • dateTime - The dateTime value of which the second must be returned.

    Return type

    • integer

    Examples

    ExpressionResultTypeSECOND ( DATETIME ( 2015 , 5 , 25 , 00 , 00 , 00 ) )0IntegerSECOND ( DATETIME ( 2015 , 5 , 25 , 23 , 45 , 59 ) )59Integer

    Back to top

    Panel
    bgColorwhite

    TODAY

    This function returns the current date of the server. The date is given as base type date, formatted as dd-mm-yyyy.

    Syntax

    Code Block
    TODAY

    Return type

    • date
    UI Text Box
    typenote
    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

    Panel
    bgColorwhite

    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

    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.

    Back to top

    ...

    bgColorwhite

    WEEKNUMBER

    This function 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 )

    Inputs

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

    Return type

    • integer

    Examples

    ...

    Back to top

    ...

    bgColorwhite

    DAYOFWEEK

    ...

    • Sunday = 1
    • Monday = 2
    • Tuesday = 3
    • Wednesday = 4
    • Thursday = 5
    • Friday = 6
    • Saturday = 7

    ...

    Code Block
    DAYOFWEEK ( date | dateTime )
    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

    ...

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

    ...

    • integer
    Examples

    ...


    Back to top