Date and Time Formula Functions

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:

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

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