Date and Time Formula Functions

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)) </syntaxhighlight>

Syntax: <syntaxhighlight lang="java" enclose="div">

DATEPART('dString')) </syntaxhighlight>

Return: A date in the format defined by Date Format

Examples: <syntaxhighlight lang="java" enclose="div">

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' </syntaxhighlight>

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

Search

where to start

Feedback

customize and design

resources

Tools

Personal tools

Date and Time Formula Functions

DATE

DATEADD

DATECOMP

DATEPART

DATESUB

DATEVALUE

DAY

HOUR

MINUTE

MONTH

NOW

NULLDATE

NULLTIME

SECOND

TIME

TIMEPART

TIMEVALUE

TODAY

WEEKDAY

YEAR