Page History

SUBTRACT PERIOD FROM DATETIME

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

Syntax

Panel

bgColor	white

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

Expression	Result	Type
01-01-2015 – 1 DAYS	31-12-2014	Date
01-01-2015 – 1 WEEKS	25-12-2014	Date
01-01-2015 – 1 MONTHS	01-12-2014	Date
01-01-2015 – 1 YEARS	01-01-2014	Date

Back to top

Panel

bgColor	white

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

bgColor	white

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

type	note

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

type	note

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

type	note

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

bgColor	white

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: SUBTRACT PERIOD FROM DATE function, e.g. Sunday = 1, Monday = 2 etc.

UI Text Box

type	note

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

bgColor	white

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

bgColor	white

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

bgColor	white

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

bgColor	white

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

bgColor	white

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

bgColor	white

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

bgColor	white

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

type	note

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

bgColor	white

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

type	note

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

...

bgColor	white

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

...

bgColor	white

DAYOFWEEK

...

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

...

Code Block
DAYOFWEEK ( date \| dateTime )

...

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

...

integer

Examples

...

Back to top

Space shortcuts

Page tree

Versions Compared

Old Version 1

New Version 2

Key

SUBTRACT PERIOD FROM DATETIME

SUBTRACT PERIOD FROM DATE

PERIOD BETWEEN

WORKINGDAYS BETWEEN

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

TODAY

NOW

WEEKNUMBER

DAYOFWEEK