<< 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.
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
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.
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
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
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
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
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
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)
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
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
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)
The TIME function returns the current time of day and takes no parameters.
TIME()
Aggregate = No
Example
TIME() = HH:mm:ss (current time)
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
The TODAY function returns the current date with time set to 0:00:00.
TODAY()
Aggregate = No
Example
TODAY() = yyyy/MM/dd (current date)
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
Copyright © 2022 EarthSoft, Inc • Modified: 02 Aug 2021