<< Click to Display Table of Contents >> Navigation:  Collect > Collect Enterprise > Template 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 attempts to convert the optional parameter to a date-time value. If the parameter is NULL or cannot be converted to a date-time, then the function returns NULL. If no parameter is passed, then the function returns the current date-time.

DATETIME(<DateTimeParameter_1>)

Aggregate = No

 

Example 1 – Collect Form1

 

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

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

 

Example 2

 

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. If the first parameter is NULL, then the function returns NULL. If the optional second parameter is “name”, then the function returns the days name. If the second parameter is “dayofyear”, then the function returns the day number.

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

Aggregate = No

 

Example 1

 

DAY([FieldA]) = 15

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

 

Example 2

 

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

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

 

Example 3

 

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 can take up to two optional parameters and returns a string representation of a date-time. The first parameter specifies the date-time value to be formatted, and the second parameter specifies the format of the result string. If no parameters are passed, the function returns the current date in short date format (yyyy/MM/dd). If the first parameter is NULL or cannot be converted to a valid datetime value, then the function returns NULL. If the second parameter is not passed or is NULL, then the short date format is used.

FORMATDATE(<DateTimeParameter_1>,<StringParameter_1>)

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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. If the parameter is NULL or cannot be converted to a valid date-time, then the function returns NULL.

HOUR(<DateTimeParameter_1>)

Aggregate = No

 

Example 1

 

HOUR([FieldA]) = 10

 

Example 2

 

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. If the parameter is NULL or cannot be converted to a valid date-time, then the function returns NULL.

LONGDATE(<DateTimeParameter_1>)

Aggregate = No

 

Example  

 

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

MINUTE

The MINUTE function returns the minute number from a specified time or date-time. If the parameter cannot be converted to a valid date-time, then the function returns NULL.

MINUTE(<DateTimeParameter_1>)

Aggregate = No

 

Example 1

 

MINUTE([FieldA]) = 22

 

Example 2

 

MINUTE([FieldA]) = 22

MONTH

The MONTH function returns the month number or name from a specified date or date-time. If the first parameter cannot be converted to a valid date-time value, then the function returns NULL. If the second parameter is “name”, then the function returns the name of the month. Otherwise, the function returns the month's number.

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

Aggregate = No

 

Example 1

 

MONTH([FieldA]) = 4

 

Example 2

 

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

NOW

The NOW function returns the current date and time and takes no parameters.

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. If the parameter cannot be converted to a valid date-time value, then the function returns NULL.

QUARTER(<DateTimeParameter_1>)

Aggregate = No

 

Example

 

QUARTER([FieldA]) = 2

SECOND

The SECOND function returns the seconds number from a specified time or date-time. If the parameter cannot be converted to a valid date-time, then the function returns NULL.

SECOND(<DateTimeParameter_1>)

Aggregate = No

 

Example

 

SECOND([FieldA]) = 57

SHORTDATE

The SHORTDATE function formats the optional parameter as a short date-time (yyyy/MM/dd). If no parameter is passed, then the function returns the current date in short date format. If the parameter is NULL or cannot be converted to a valid date-time value, then the function returns NULL.

SHORTDATE(<DateTimeParameter_1>)

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

TIME

The TIME function returns the current time of day and takes no parameters.

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 or if the third parameter is not null and not “s”,”m”,”h” or “d”, the function returns a standard time-span. If the third parameter is NULL, then the “d” specifier is used. If the first or second parameter cannot be converted to valid date-time values, then the function returns NULL.

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

Aggregate = No

 

Example 1

 

TIMESPAN([FieldA],[FieldB]) = 1.20:11:21 (standard time span notation => 1 day, 20 hours, 11 minutes, 21 seconds)

 

Example 2

 

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

TODAY

The TODAY function returns the current date with time set to 0:00:00.

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. If the parameter cannot be converted to a valid date-time, then it returns NULL.

YEAR(<DateTimeParameter_1>)

Aggregate = No

 

Example

 

YEAR([FieldA]) = 2019