Date & time functionsFunctions you can use in Boost.space Integrator - create, update, delete, get, search.
Prev	Functions	Next

Date & time functions

formatDate (date; format; [timezone])

When to use it: You have a Date value that you wish to convert (format) to a Text value (textual human readable representation) like 12-10-2019 20:30 or Aug 18, 2019 10:00 AM

Parameters

The second column indicates the expected type. If different type is provided, Type Coercion is applied.

date Date Date value to be converted to a Text value.

format

Text

Format specified using Tokens for date/time formatting.

Example: DD.MM.YYYY HH:mm

timezone

Text

Optional. The timezone used for the conversion.

See List of tz database time zones, column “TZ database name” for the list of recognized timezones.

If omitted, the Organization’s timezone specified in your Profile settings will be applied (tab: Time zone options, section: ScenariosA specific connection between applications in which data can be transferred. Two types of scenarios: active/inactive. More, row: the organization the scenarioA specific connection between applications in which data can be transferred. Two types of scenarios: active/inactive. More belongs to).

Examples: Europe/Prague, UTC

	Note
	Only the values listed in the “TZ database name” column are recognized by the functionFunctions you can use in Boost.space Integrator - create, update, delete, get, search. as a valid timezone. Any other value will be ignored and the Organization’s timezone specified in your Profile will be used instead.

Return value and type

Text representation of the given Date value according to the specified format and timezone. Type is Text.

Examples

The Organization’s and Web’s timezone were both set to Europe/Prague in the following examples.

Example 5. formatDate( 1. Date created ; MM/DD/YYYY )

= 10/01/2018

Example 6. formatDate( 1. Date created ; YYYY-MM-DD hh:mm A )

= 2018-10-01 09:32 AM

Example 7. formatDate( 1. Date created ; DD.MM.YYYY HH:mm ; UTC )

= 01.10.2018 07:32

Example 8. formatDate( now ; MM/DD/YYYY HH:mm )

= 19.03.2019 15:30

parseDate (text; format; [timezone])

When to use it: You have a Text value representing a date (e.g. 12-10-2019 20:30 or Aug 18, 2019 10:00 AM) and you wish to convert (parse) it to a Date value (binary machine readable representation).

Parameters

The second column indicates the expected type. If different type is provided, Type Coercion is applied.

text Text Text value to be converted to a Date value.

format

Text

Format specified using Tokens for date/time parsing.

Example: DD.MM.YYYY HH:mm

timezone

Text

Optional. The timezone used for the conversion.

See List of tz database time zones, column “TZ database name” for the list of recognized timezones.

If omitted, the Organization’s timezone specified in your Profile settings will be applied (tab: Time zone options, section: Scenarios, row: the organization the scenario belongs to).

Examples: Europe/Prague, UTC

	Note
	Only the values listed in the “TZ database name” column are recognized by the function as a valid timezone. Any other value will be ignored and the Organization’s timezone specified in your Profile will be used instead.

Return value and type

Date representation of the given Text value according to the specified format and timezone. Type is Date.

Examples

Please note that in the following examples the returned Date value is expressed according to ISO 8601, but the actual resulting value is of type Date.

Example 9. parseDate( 2016-12-28 ; YYYY-MM-DD )

= 2016-12-28T00:00:00.000Z

Example 10. parseDate( 2016-12-28 16:03 ; YYYY-MM-DD HH:mm )

= 2016-12-28T16:03:00.000Z

Example 11. parseDate( 2016-12-28 04:03 pm ; YYYY-MM-DD hh:mm a )

= 2016-12-28T16:03:06.000Z

Example 12. parseDate( 1482940986 ; X )

= 2016-12-28T16:03:06.000Z

addDays (date; number)

Returns a new date as a result of adding a given number of days to a date. To subtract days, enter a negative number.

addDays( 2016-12-08T15:55:57.536Z ; 2 )

= 2016-12-10T15:55:57.536Z

addDays( 2016-12-08T15:55:57.536Z ; -2 )

= 2016-12-6T15:55:57.536Z

addHours (date; number)

Returns a new date as a result of adding a given number of hours to a date. To subtract hours, enter a negative number.

addHours( 2016-12-08T15:55:57.536Z ; 2 )

= 2016-12-08T17:55:57.536Z

addHours( 2016-12-08T15:55:57.536Z ; -2 )

= 2016-12-08T13:55:57.536Z

addMinutes (date; number)

Returns a new date as a result of adding a given number of minutes to a date. To subtract minutes, enter a negative number.

addMinutes( 2016-12-08T15:55:57.536Z ; 2 )

= 2016-12-08T15:57:57.536Z

addMinutes( 2016-12-08T15:55:57.536Z ; -2 )

= 2016-12-08T15:53:57.536Z

addMonths (date; number)

Returns a new date as a result of adding a given number of months to a date. To subtract months, enter a negative number.

addMonths( 2016-12-08T15:55:57.536Z ; 2 )

= 2016-10-08T15:55:57.536Z

addMonths( 2016-12-08T15:55:57.536Z ; -2 )

= 2016-06-08T15:55:57.536Z

addSeconds (date; number)

Returns a new date as a result of adding a given number of seconds to a date. To subtract seconds, enter a negative number.

addSeconds( 2016-12-08T15:55:57.536Z ; 2 )

= 2016-12-08T15:55:59.536Z

addSeconds( 2016-12-08T15:55:57.536Z ; -2 )

= 2016-12-08T15:55:55.536Z

addYears (date; years)

Returns a new date as a result of adding a given number of years to a date. To subtract years, enter a negative number.

addYears( 2016-12-08T15:55:57.536Z ; 2 )

= 2018-08-08T15:55:57.536Z

addYears( 2016-12-08T15:55:57.536Z ; -2 )

= 2014-08-08T15:55:57.536Z

setSecond (date; number)

Returns a new date with the seconds specified in parameters. Accepts numbers from 0 to 59. If a number is given outside of this range, it will return the date with the seconds from the previous or subsequent minute(s), accordingly.

setSecond( 2015-10-07T11:36:39.138Z ; 10 )

= 2015-10-07T11:36:10.138Z

setSecond( 2015-10-07T11:36:39.138Z ; 61 )

= 2015-10-07T11:37:01.138Z

setMinute (date; number)

Returns a new date with the minutes specified in parameters. Accepts numbers from 0 to 59. If a number is given outside of this range, it will return the date with the minutes from the previous or subsequent hour(s), accordingly.

setMinute( 2015-10-07T11:36:39.138Z ; 10 )

= 2015-10-07T11:10:39.138Z

setMinute( 2015-10-07T11:36:39.138Z ; 61 )

= 2015-10-07T12:01:39.138Z

setHour (date; number)

Returns a new date with the hour specified in parameters. Accepts numbers from 0 to 59. If a number is given outside of this range, it will return the date with the hour from the previous or subsequent day(s), accordingly.

setHour( 2015-10-07T11:36:39.138Z ; 10 )

= 2015-08-07T06:36:39.138Z

setHour( 2015-10-07T11:36:39.138Z ; 61 )

= 2015-08-06T18:36:39.138Z

setDay (date; number/name of the day in english)

Returns a new date with the day specified in parameters. It can be used to set the day of the week, with Sunday as 1 and Saturday as 7. If the given value is from 1 to 7, the resulting date will be within the current (Sunday-to-Saturday) week. If a number is given outside of the range, it will return the day from the previous or subsequent week(s), accordingly.

setDay( 2018-06-27T11:36:39.138Z ; monday )

= 2018-06-25T11:36:39.138Z

setDay( 2018-06-27T11:36:39.138Z ; 1 )

= 2018-06-24T11:36:39.138Z

setDay( 2018-06-27T11:36:39.138Z ; 7 )

= 2018-06-30T11:36:39.138Z

setDate (date; number)

Returns a new date with the day of the month specified in parameters. Accepts numbers from 1 to 31. If a number is given outside of the range, it will return the day from the previous or subsequent month(s), accordingly.

setDate( 2015-08-07T11:36:39.138Z ; 5 )

= 2015-08-05T11:36:39.138Z

setDate( 2015-08-07T11:36:39.138Z ; 32 )

= 2015-09-01T11:36:39.138Z

setMonth (date; number/name of the month in English)

Returns a new date with the month specified in parameters. Accepts numbers from 1 to 12. If a number is given outside of this range, it will return the month in the previous or subsequent year(s), accordingly.

setMonth( 2015-08-07T11:36:39.138Z ; 5 )

= 2015-05-07T11:36:39.138Z

setMonth( 2015-08-07T11:36:39.138Z ; 17 )

= 2016-05-07T11:36:39.138Z

setMonth( 2015-08-07T11:36:39.138Z ; january )

= 2015-01-07T12:36:39.138Z

setYear (date; number)

Returns a new date with the year specified in parameters.

setYear( 2015-08-07T11:36:39.138Zv ; 2017 )

= 2017-08-07T11:36:39.138Z

Examples

How to calculate n-th day of week in month

	Note
	Adapted for Boost.spaceCentralization and synchronization platform, where you can organize and manage your data. More IntegratorPart of the Boost.space system, where you can create your connections and automate your processes. More from this original source

If you need to calculate a date corresponding to n-th day of week in month (e.g. 1st Tuesday, 3rd Friday, etc.), you may use the following formula:

You may copy and paste the formula’s code into a field:

{{addDays(setDate(1.date; 1); 1.n * 7 - formatDate(addDays(setDate(1.date; 1); "-" + 1.dow); "E"))}}

The formula contains the following itemsItems are rows in records (order/request/invoice/purchase...):

`1.n`	n-th day: `1` for 1st Tuesday `2` for 2nd Tuesday `3` for 3rd Tuesday etc.
`2.dow`	day of the week `1` for Monday `2` for Tuesday `3` for Wednesday `4` for Thursday `5` for Friday `6` for Saturday `7` for Sunday
`1.date`	The date determines the month. To calculate n-th day of week in current month use the `now` variable.

In case you wish to calculate only one specific case, e.g. 2nd Wednesday, you may replace the items 1.n and 2.dow in the formula with corresponding numbers. For 2nd Wednesday in current month you would use the following values:

1.n = 2
1.dow = 3
1.date = now

Explanation:

setDate(now;1) returns first of current month
formatDate(....;E) returns day of week (1, 2, … 6)
see the original source for the rest

How to calculate days between dates

One possibility is to employ the following expression:

You can copy & paste the following code:

{{round((2.value - 1.value) / 1000 / 60 / 60 / 24)}}

	Note
	Values of `D1` and `D2` have to be of type Date. If they are of type String (e.g. “20.10.2018”), use `parseDate()` function to convert them to type Date.

	Note
	The `round()` function is used for cases when one of the dates falls within the daylight savings time period and the other not. In these cases, the difference in hours is by one hour less/more and dividing it by 24 gives a non-integer results.

How to calculate last day/millisecond of month

When specifying a date range (e.g. in a search moduleThe module is an application or tool within the Boost.space system. The entire system is built on this concept of modularity. (module - Contacts) More) spanning the whole previous month as closed interval (the interval that includes both its limit points), it is necessary to calculate last day of month.

2019-09-01 ≤ D ≤ 2019-09-30

The formula below shows one way how to calculate last day of the previous month:

You can copy and paste the following code:

{{addDays(setDate(now; 1); -1)}}

In some cases, it is necessary to calculate not only the last day of month, but literally its last millisecond:

2019-09-01T00:00:00.000Z ≤ D ≤ 2019-09-30T23:59:59.999Z

The formula below shows one way how to calculate last millisecond of the previous month:

You can copy and paste the following code:

{{parseDate(parseDate(formatDate(now; "YYYYMM01"); "YYYYMMDD"; "UTC") - 1; "x")}}

If the result should respect your timezone settings, simply omit the UTC argument:

{{parseDate(parseDate(formatDate(now; "YYYYMM01"); "YYYYMMDD") - 1; "x")}}

However, it is preferable to use half-open interval instead (the interval that excludes one of its limit points), specifying the first day of the following month instead and replacing the less or equal than operator with less than:

2019-09-01 ≤ D < 2019-10-01

2019-09-01T00:00:00.000Z ≤ D < 2019-10-01T00:00:00.000Z

How to transform seconds into hours, minutes and second

One possibility is to use the following expression shown below:

You can copy and paste the following code:

{{floor(1.seconds / 3600)}}:{{floor((1.seconds % 3600) / 60)}}:{{((1.seconds % 3600) % 60)}}

	Note
	Values of `Second` should be number type. This function is suited only if the second value is less than 86400 ( less than a day ).

Prev	Up	Next
Certificates and keys	Home	Tokens for date/time parsing