AgileApps Support Wiki

Date and Time Formula Functions

From AgileApps Support Wiki
Revision as of 00:35, 19 October 2013 by imported>Aeric
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Day, Date, and Time-related functions, for use in formulas.

Learn more:

Contents:

DATE

DATEADD

DATECOMP

DATEPART

DATESUB

DATEVALUE

DAY

HOUR

MINUTE

MONTH

NOW

NULLDATE

NULLTIME

SECOND

TIME

TIMEPART

TIMEVALUE

TODAY

WEEKDAY

YEAR

DATE

The DATE function creates a date in the standard date format from a text string representing the day, month and year. You can also optionally include the time with this function.

Syntax
DATE(year, month, day [,hour, minute, second])
Return
Date in the format defined by Date Format
Example

DATE (1988, 3, 7, 3, 22, 45) = '03/07/1988 03:22:45'

DATEADD

The DATEADD function adds a number to an existing date, date/time, or time variable.

Syntax
DATEADD('dString', number[,DAY or MONTH or YEAR or HOUR or MINUTE])

DATEADD('time field', number[,DAY or MONTH or YEAR or HOUR or MINUTE])

DATEADD('date field', number[,DAY or MONTH or YEAR or HOUR or MINUTE])

DATEADD('time and date field', number[,DAY or MONTH or YEAR or HOUR or MINUTE])

where number represents a day, month, year, hour, or minute.

If the first argument is of type time, only hour or minute are allowed as the last argument.

Return
Date in the format defined by Date Format
Examples
DATEADD('11/24/1963', 6) = '11/30/1963 12:00 AM'
DATEADD('01/24/1964 12:12 PM', 6, 'MONTH') = '07/24/1964 12;12 PM'
DATEADD('02:12 PM', 40) = '02:52 PM'

DATECOMP

The DATECOMP function compares two given dates, datetimes, or times and returns:

  • For two given dates or datetimes, the difference in days
  • For two given times, the difference in minutes


Syntax
DATECOMP(dString, dString)

See Date Format for more detail on the parameters used in this function.

If the first argument is of type time, only hour or minute are allowed as the last argument.

Return
Number
Example
DATECOMP('04/15/2010', '04/05/2010') = 10
DATECOMP('04/05/2010', '04/15/2010') = -10
DATECOMP(TODAY(), '04/05/2010') = 10 when TODAY is 04/15/2010 
DATECOMP(TODAY(), Closed_Date) = 10 when TODAY is 04/15/2010 and Closed_Date is 04/05/2010
DATECOMP('04/15/2010 10:15 am', '04/05/2010 1:15 am') = 10
DATECOMP('10:23 PM', '2:52 AM') = 1171.0
DATECOMP('10:23 AM', '2:52 PM') = -269.0

DATEPART

The DATEPART function returns a date/time value that is normalized to the start of the day, to match other date/time values on the same day.

Note:
To get a string that contains the date only, use this:
TEXT(MONTH(date_modified))+'/'+TEXT(DAY(date_modified))+'/'+TEXT(YEAR(date_modified))
Syntax
DATEPART('<tt>[[dString]]</tt>'))
Return
A date in the format defined by Date Format
Examples
DATEPART('04/22/2009 03:15 am') = '04/22/2009 12:00:00 AM'
 
DATEPART(DATE ('2009', '04', '22', '03', '15',  '00')) = '04/22/2009 12:00:00 AM'


DATESUB

The DATESUB function does one of two things:

  • Subtracts a number from a date, datetime, or time variable, and returns a date
  • Subtracts a date, datetime, or time variable from another date, datetime, or time variable, and returns a number
Syntax
 DATESUB('dString', 'dString' or number [,'DAY' or 'MONTH' or 'YEAR' or 'HOUR' or 'MINUTE'])
Return
A date in the format defined by Date Format
An integer representing the number of days
Example
DATESUB('12/06/2010', 2, 'MONTH') = '10/06/2010'
DATESUB('12/06/2010', 2, 'YEAR') = '12/06/2008'
DATESUB('12/06/2010', 2, 'DAY') = '12/04/2010'
DATESUB('12/26/2010', '12/06/2010') = 20


See Date Format for more detail on the parameters used in this function.

DATEVALUE

The DATEVALUE function creates a date from a datetime or text expression

Syntax
DATEVALUE('dString')
DATEVALUE('string')


The format must match the date format specified in the Company Information section. Day, month, and year are required; hour and minute are optional.

Return
Date
Examples
DATEVALUE('06/23/2010 1:27 pm') = 06/23/2010
DATEVALUE('06/23/2011') = 06/23/2011


See Date Format for more detail on the parameters used in this function.

DAY

The DAY function reads a date and returns the day value.

Syntax
DAY(dString)
Return
Number in the format: dd
Range 1 - 31


Example
DAY('09/21/2010') = 21

HOUR

The HOUR function reads a date and returns the hour value.

Syntax
HOUR(dString)
Return
Number in the format: hh
Range 0 (12:00 AM) - 23 (11:00 PM)
Example;
HOUR('06/13/2006 12:45 am') = 0

MINUTE

The MINUTE function reads a date and returns the minute value.

Syntax
MINUTE('dString')
Return
A number in the format: mm
Range 0 - 59
Example
MINUTE('06/13/2010 12:45 pm') = 45

MONTH

The MONTH function reads a date and returns the month value.

Syntax
MONTH('dString')

Date, month, and year are required; hour and minute are optional.

Return
Number in the format: mm
Range 1 (January) - 12 (December)
Example
MONTH('06/13/2006') = 06

NOW

The NOW function returns the current date and time.

Syntax
NOW()
Return
Date in the format: 'Date Format'
Example
NOW() = '07/23/2004 12:45 pm'

NULLDATE

The NULLDATE function returns a empty (null/blank) date.

Syntax
NULLDATE()
Return
Date in Date Format
Example
IF(ISNULL(NULLDATE()), 'TRUE', 'FALSE')

NULLTIME

The NULLTIMEfunction returns a empty (null/blank) time.

Syntax
NULLTIME()
Return
Time in Time Format

SECOND

The SECOND function reads a date and returns the seconds portion of the time.

Syntax
SECOND(date)
Return
A number in the format: ss
Range 0 - 59
Example
SECOND('06/23/2010 12:45') = 00

TIME

The TIME function creates a datetime from hours and minutes.

Syntax
TIME(hours, minutes)
Return
Date in the format defined by Date Format
Example
TIME(2,45) = '02:45:00'
TIME(15,45) = '15:45:00'

TIMEPART

The TIMEPART function returns the time part from a date/datetime.

Syntax
TIMEPART('dString')
Return
Date in the format defined by Date Format
Example
TIMEPART('06/12/2009 10:15 am') = '10:15:00'

TIMEVALUE

The TIMEVALUE function creates a time from text in 24 hour format.

Syntax
TIMEVALUE (expression)
Return
Date in the format defined by Date Format
Range, depending on format
24-hour format: [0-23]:[0-59]
12-hour format: [1-12]:[0-59] [AM/PM]


Example
TIMEVALUE('01:57') = '01:57:00'
TIMEVALUE('01:57 pm') = '13:57:00'
TIMEVALUE('15:57') = '15:57:00'

TODAY

The TODAY function returns a date value representing the current date.

Syntax
TODAY()
Return
Date in the format: 'Date Format'
Example
On February 14, 2011, this function would create this result:
TODAY() = '02/14/2011'

WEEKDAY

The WEEKDAY function evaluates a date and returns the day of the week.

Syntax
WEEKDAY('dString' [, return_type])

Day, month, and year are required; hour and minute are optional.

The return_type parameter specifies the day that the week starts:

  • 0 or 1: Week starts on Sunday (Sunday is day one) (default)
  • 2: Week starts on Monday (Monday is day one)
Return
Number
Range 1-7
Example, where 08/10/2009 is a Monday
WEEKDAY('08/10/2009', 0) = 2
WEEKDAY('08/10/2009', 1) = 2
WEEKDAY('08/10/2009') = 2

YEAR

The YEAR function reads a date and returns the year value.

Syntax
YEAR(dString)
Return
Number in the format: yyyy
Range 1900 - 9999
Examples
YEAR('02/14/2010') = 2010
YEAR('02/14/2010 12:15 pm') = 2010
Retrieved from "https://agileappscloud.info/aawiki-src/index.php?title=Date_and_Time_Formula_Functions&oldid=22578"