Date

Date

From LongJump Support Wiki

Date
Jump to: navigation, search

Day, Date and Time-related Formula Functions.

Learn more: Date/Time Formats

DATE

DATEADD

DATECOMP

DATEPART

DATESUB

DATEVALUE

DAY

HOUR

MINUTE

MONTH

NOW

NULLDATE

NULLTIME

SECOND

TIME

TIMEPART

TIMEVALUE

TODAY

WEEKDAY

YEAR

Contents

Day, Date and Time-related Formula Functions

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.

File:notepad.png

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.

File:notepad.png

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
yearyyyyA text string depicting the year (1900 - 9999)
monthmmA text string depicting the month (1-12, beginning with January)
dayddA text string depicting the day of the month (1-31, depending on the number of days in the month)
hourhhA 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)
minutemmA 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>
Personal tools