Time Functions

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

FieldA

 

 

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

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. 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

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

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. 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

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. 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  

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

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. 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

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

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

SECOND(<DateTimeParameter_1>)

Aggregate = No

 

Example

FieldA

2019/04/15 10:22:57

 

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

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

FieldA

FieldB

April 13, 2019 14:11:36

2019/04/15 10:22:57

 

TIMESPAN([FieldA],[FieldB]) = 1.20:11:21 (standard time span notation => 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 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

FieldA

2019/04/15 10:22:57

 

YEAR([FieldA]) = 2019