Difference between revisions of "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])

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

Date/Time Formats

Dates are used throughout the platform, in Appointments, as Start/End Dates in Tasks and Projects, Expected Close Date, Estimated Start/End Date, Date Due, etc.

Default Date Format

The dates in records are formatted according to the default date format specified in Personalize and Company Information section of the Setup area. Depending on your geographic location, users and organizations may prefer to have dates presented as 20.04.2009, or 04/20/2009.

Note: Date formats are case-sensitive.

These Date Formats are available in the platform:

`mm/dd/yyyy`	`dd/mm/yyyy`	`yyyy/dd/mm`	`yyyy/mm/dd`
`mm-dd-yyyy`	`dd-mm-yyyy`	`yyyy-dd-mm`	`yyyy-mm-dd`
`mm.dd.yyyy`	`dd.mm.yyyy`	`yyyy.dd.mm`	`yyyy.mm.dd`

Where

mm is "Month"

dd is "State"

yyyy is Year

Delimiters in Date Strings

Date Delimiters

(/) slash, or stroke

(-) dash, or hyphen

(.) period, dot, or full stop

Time Delimiter

(:) Colon

Date and Time Format

Depending on the location, users can choose a 12-hour clock day (with AM/PM), or a 24-hour clock day.

Note: Date/Time formats are case-sensitive.

Using a 12-hour format, '10:00 AM' is created with this syntax:

hh:mm am

Using a 24-hour format, 16:15 hours (4:15 PM) is created with this syntax:

hh:mm

Several examples are shown here:

12-hour clock day

   Date/Time Format         Syntax
   ---------------------    ------------------
   '06/12/2009 10:15 am'    mm/dd/yyyy hh:mm am
   '06-12-2009 12:15 pm'    mm-dd-yyyy hh:mm pm
   '06.12.2009 05:37 pm'    mm.dd.yyyy hh:mm pm

24-hour clock day
  
   Date/Time Format         Syntax
   ------------------       ------------------
   '06/12/2009 10:15'       mm/dd/yyyy hh:mm
   '06-12-2009 12:15'       mm-dd-yyyy hh:mm
   '06.12.2009 17:37'       mm.dd.yyyy hh:mm

Date/Time String

Date strings are used as arguments in Date/Time Formula Functions, and can also be the result of applying a Date/Time formula function.

Date String Examples

'03/27/2009 10:15 am'
'20.04.2009 10:45 pm' 
'2009-20-04 20:30'

Date strings are composed of these elements:

Element	Syntax	Description
year	yyyy	A text string depicting the year (1900 - 9999)
month	mm	A text string depicting the month (1-12, beginning with January)
day	dd	A text string depicting the day of the month (1-31, depending on the number of days in the month)
hour	hh	A text string depicting the hour in the format (0-12 for a 12-hour clock day, or 0-24 for a 24-hour clock day)
minute	mm	A text string depicting the minute in the format (0-59)
am pm	a p	A text string depicting am/pm in a 12-hour clock day For more information, see http://en.wikipedia.org/wiki/12-hour_clock

For Developers

Date Expressions

Expression	Result
`DATESUB('06/13/2009', 2, 'MONTH')`	`= '04/13/2009'`
`DATESUB(end_date, MONTH(start_date), 'MONTH')`	`= '04/23/2009'`
`DATESUB(end_date, start_date)`	`= 140` (days)
`MONTH(end_date) - MONTH(start_date)`	`= 4` (months)

Where:

end_date = '06/23/2009'

start_date = '02/03/2009' and MONTH(start_date) = 2

Long Date Format

Resources in APIs that apply Date or DateTime values use a Long Date Format, which is different from theMM-DD-YYYY date patterns described in the UI.

Long Date Format is based on the Java class Date, which is defined as the number of milliseconds since January 1, 1970, 00:00:00 GMT.

This Long Date Format encodes dates and times as a number, which can then be applied to complex filters and formulas in the LongJump Platform.

Example

<someDate>1228457520000</someDate>

@@ Line 1: / Line 1: @@
-Day, Date and Time-related [[Formula Functions]]. Learn more: [[#Date Formats|Date Formats]]
+Day, Date and Time-related [[Formula Functions]].
+:''Learn more:'' [[#Date/Time Formats|Date/Time Formats]]
 {| border="0" cellpadding="5" cellspacing="0" width="50%"

Search

where to start

learn more

Tools

Personal tools

Difference between revisions of "Date"

Latest revision as of 00:47, 24 November 2011

Contents

Day, Date and Time-related Formula Functions

DATE

DATEADD

DATECOMP

DATEPART

DATESUB

DATEVALUE

DAY

HOUR

MINUTE

MONTH

NOW

NULLDATE

NULLTIME

SECOND

TIME

TIMEPART

TIMEVALUE

TODAY

WEEKDAY

YEAR

Date/Time Formats

Default Date Format

Date and Time Format

Date/Time String

For Developers

Date Expressions

Long Date Format