Time Functions

<< Click to Display Table of Contents >>

 >Collect > Collect Enterprise > Form Designer Page > Formula Builder > Functions >

Time Functions

The Time functions allow for control of temporal parameters. Within EQuIS Collect, the functions perform calculations as data are entered in forms on the Mobile app. Within EQuIS Link, the functions provide a means to transform data when loading to a selected format.  

 

Below is a descriptive list of each time function and its operations. The examples are applicable to either fields in a Collect form or an EQuIS Link data source, unless otherwise noted.

DATETIME

The DATETIME function examines if one parameter is met, then the function attempts to convert it to a date-time format. Otherwise, the function returns the current date-time.

DATETIME(<DateTimeParameter_1>)

Aggregate = No

 

Example 1 – Collect Form1

FieldA

 

 

DATETIME([FieldA]) = yyyy/MM/dd HH:mm:ss (current Date-Time)

This formula is returning the current date and time as denoted on the field device.

 

Example 2

FieldA

04-15-2019

 

DATETIME([FieldA]) = 2019/04/15 10:17:53 AM

DAY

The DAY function returns the day number, name, or day of the year from a specified date or date-time.

DAY(<DateTimeParameter_1>,{<OptionalStringParameter_1>})

Aggregate = No

 

Example 1

FieldA

2019/04/15

 

DAY([FieldA]) = 15

This formula is returning the number associated with the day of the month for the date in FieldA.

 

Example 2

FieldA

2019/04/15

 

DAY([FieldA],’name’) = Monday

This formula is returning the name of the day for the date in FieldA.

 

Example 3

FieldA

2019/04/15

 

DAY([FieldA],’dayofyear’) = 105

This formula is returning the number associated with the day of the year for the date in FieldA.

FORMATDATE

The FORMATDATE function examines if one parameter is met, then the function attempts to convert it to a short date (yyyy/MM/dd). If two parameters are passed, then the function uses the second parameter as the format string. If no parameters are passed, the function returns the current date as a short date.

FORMATDATE(<DateTimeParameter_1>,<StringParameter_1>,<Parameter_2>)

Aggregate = No

 

Example 1

FieldA

2019-04-15

 

FORMATDATE([FieldA],) = 2019/04/15

 

Example 2

FieldA

April 15, 2019 10:11:23

 

FORMATDATE([FieldA],'MM/dd/yyyy HH:mm:ss') = 04/15/2019 10:11:23

HOUR

The HOUR function returns the hour number from a specified time or date-time.

HOUR(<DateTimeParameter_1>)

Aggregate = No

 

Example 1

FieldA

2019/04/15 10:22:57

 

HOUR([FieldA]) = 10

 

Example 2

FieldA

10:22:57

 

HOUR([FieldA]) = 10

LONGDATE

The LONGDATE function converts the specified date or date-time value to a long-date format. If no value is specified, the current date-time is converted to long-date format.

LONGDATE(<DateTimeParameter_1>)

Aggregate = No

 

Example  

FieldA

2019/04/15 10:22:57

 

LONGDATE([FieldA]) = Monday, April 15, 2019

MINUTE

The MINUTE function returns the minute number from a specified time or date-time.

MINUTE(<DateTimeParameter_1>)

Aggregate = No

 

Example 1

FieldA

2019/04/15 10:22:57

 

MINUTE([FieldA]) = 22

 

Example 2

FieldA

10:22:57

 

MINUTE([FieldA]) = 22

MONTH

The MONTH function returns the month number or name from a specified date or date-time.

MONTH(<DateTimeParameter_1>,{<OptionalStringParameter_1>})

Aggregate = No

 

Example 1

FieldA

2019/04/15 10:22:57

 

MONTH([FieldA]) = 4

 

Example 2

FieldA

2019/04/15

 

MONTH([FieldA],’name’) = April

NOW

The NOW function returns the current date and time.

NOW()

Aggregate = No

 

Example

NOW() = yyyy/MM/dd HH:mm:ss (current date-time)

QUARTER

The QUARTER function returns the quarter number of the year from a specified date or date-time.

QUARTER(<DateTimeParameter_1>)

Aggregate = No

 

Example

FieldA

2019/04/15 10:22:57

 

QUARTER([FieldA]) = 2

SECOND

The SECOND function returns the seconds number from a specified time or date-time.

SECOND(<DateTimeParameter_1>)

Aggregate = No

 

Example

FieldA

2019/04/15 10:22:57

 

SECOND([FieldA]) = 57

SHORTDATE

The SHORTDATE function examines if one parameter is met, then the function attempts to convert it to a short date. Otherwise, the function returns the current date in short date format (yyyy/MM/dd).

SHORTDATE(<DateTimeParameter_1>)

Aggregate = No

 

Example 1

FieldA

2019-04-15 10:22:57

 

SHORTDATE([FieldA]) = 2019/04/15

 

Example 2

FieldA


 

SHORTDATE([FieldA]) = yyyy/MM/dd (current date)

TIME

The TIME function returns the current time.

TIME()

Aggregate = No

 

Example

TIME() = HH:mm:ss (current time)

TIMESPAN

The TIMESPAN function calculates the time difference between two date-time parameters. The third parameter is optional and specifies the unit returned: seconds (s), minutes (m), hours (h), or days (d). If no third parameter is entered and the time span is greater than 24 hours, the function defaults to days; less than 24 hours, the function defaults to hours; and less than an hour, the function defaults to minutes. The function requires both date and time.

TIMESPAN(<DateTimeParameter_1>,<DateTimeParameter_2>,{<OptionalStringParameter_1>})

Aggregate = No

 

Example 1

FieldA

FieldB

April 13, 2019 14:11:36

2019/04/15 10:22:57

 

TIMESPAN([FieldA],[FieldB]) = 1.20:11:21 (1 day, 20 hours, 11 minutes, 21 seconds)

 

Example 2

FieldA

FieldB

April 13, 2019 14:11:36

2019/04/15 10:22:57

 

TIMESPAN([FieldA],[FieldB],’m’) = 2651.35

TODAY

The TODAY function returns the current date.

TODAY()

Aggregate = No

 

Example

TODAY() = yyyy/MM/dd (current date)

YEAR

The YEAR function returns the year number from a specified date or date-time.

YEAR(<DateTimeParameter_1>)

Aggregate = No

 

Example

FieldA

2019/04/15 10:22:57

 

YEAR([FieldA]) = 2019