Using JMP > Formula Functions Reference > Date Time Functions
Publication date: 08/13/2020

Date Time Functions

JMP stores dates and times in numeric columns using macOS standard of the number of seconds since January 1, 1904. When a column has date values, you can assign a date format to that column by double-clicking a column name and selecting Date or Time from the Format menu. See Numeric Format Options in the The Column Info Window section.

See Date and Time Functions in the JSL Syntax Reference for more information about syntax.

In Minutes, In Hours, In Days, In Weeks, In Years

Converts from the units of the function name to the equivalent number of seconds for the argument. The argument must be a number or numeric expression. For example, In Minutes(2) yields 120, and In Years(1) yields 31,557,600 (60 seconds * 60 minutes * 24 hours * 365.25 days).

Date DMY, Date MDY

Accepts numeric expressions for day, month, and year and return the associated JMP date. For example, Date DMY (20, 3, 1991) and Date MDY(3, 20, 1991) evaluate to 2,752,272,000.

Today

Returns the number of seconds between January 1, 1904 and the current date. For example, at midnight on March 20, 1991 (a Wednesday), the Today function returns 2752272000 (2,752,272,000 seconds) and continues counting. If you evaluate the Today function later in the day, it reflects the additional seconds.

Day, Month, Year

Returns the day of the month, the month (as a number from 1 to 12), a four-digit year, respectively. The argument for these functions is interpreted as a JMP date. For example, on March 20, 1991:

Day(2752272000) returns the number 20.

Month(2752272000) returns the number 3.

Year(2752272000) returns the number 1991.

Quarter

Returns the annual quarter of a datetime value as an integer 1-4.

Hour, Minute, Second

Returns the hour, the minute, and the seconds of a date-time value, respectively. The argument for these functions is interpreted as a JMP date. For example, on March 20, 1991:

Hour(2752572649) returns the number 11.

Minute(2752572649) returns the number 30.

Second(2752572649) returns the number 49.

Day of Week, Day of Year, Week of Year, Time of Day

The argument for these functions is a JMP date. Day Of Week returns a number from 1 to 7, where 1 represents Sunday. Day Of Year returns the number of days from the beginning of the year. Week Of Year returns a number from 1 to 52 based on the rule specified. Rule 1 (default) has weeks start on Sunday with the first Sunday being week 2 and week 1 is a partial week or empty; rule 2 has the first Sunday begins week 1 with any previous days being week 0; rule 3 returns the ISO week number where the week starts on Monday and week 1 is the first week of the year with four days in that year. With ISO weeks, it is possible for the first or last three days of the year to belong to the neighboring year’s week number. Time Of Day returns a number from 0 to 86399 (time of day in seconds). For example, on Wednesday, March 20, 1991:

Day Of Week(2752272000) returns the number 4.

Day Of Year(2752272000) returns the number 79.

Week Of Year(2752272000) returns the number 12.

Time Of Day(2752272000) returns the number 0.

Informat

The argument for the Informat function is a date character string. For example, Informat("03/20/1991") returns the appropriate JMP date value, 2752272000. JMP can read all the date formats except for Abbrev Date and Long Date.

Abbrev Date, Long Date, Short Date

The argument for these date functions is a JMP date. They return character strings that are the formatted representation of the argument. For example:

Abbrev Date(2752272000) returns Wed, Mar 20,1991.

Long Date(2752272000) returns Wednesday, March 20, 1991.

Short Date(2752272000) returns 3/20/91.

Format

The first argument in the Format function is a JMP date. This function returns the character string representation of the date by the date format that you specify in the second argument, which is a quoted string. If you apply this formula to a numeric column, JMP automatically changes the column’s data type to character.

You can also supply a column for the first argument and leave the rest blank. The result is the formatted value of the column reference. This can be used to extract value labels of a column when the value labels are turned off.

MDYHMS

The argument of MDYHMS is a JMP date. This function shows all date and time fields, appending zeros as time fields if no time information is present. This is useful if a date column is formulated such that not all date information is displayed. The MDYHMS function can be used to see all available date and time information.

Date Increment

Adds 1 or more intervals to a starting datetime value. For example, Date Increment(Today(), "Day", 3) adds three days to the current date. Date Increment(Today(), "Year", 3) adds 3 years to the current date.

Date Difference

Returns the difference of two datetime values. The interval argument can be Second, Minute, Hour, Day, Week, Month, Quarter, Year. The alignment arguments are described here:

Start

Used to count the number of times an interval starts.

Actual

Used to count whole intervals.

Fractional

Used to count fractional intervals.

For example, the following formula returns 207.890243055556, the number of days between the dates:

Date Difference(
	01Jan2010:00:00:00,
	27Jul2010:21:21:57,
	"Day",
	"fractional"
);

The following formula returns 207, the number of completed days between the dates:

Date Difference(
	01Jan2010:00:00:00,
	27Jul2010:21:21:57,
	"Day",
	"actual"
);

The following formula returns 9, the number of completed hours between the times:

Date Difference(
	01Jan2010:00:00:00,
	01Jan2010:09:22:57,
	"Hour",
	"actual"
);

The following formula returns 1, the number of times a new hour started between the times:

Date Difference(
	31Dec2010:23:59:59,
	01Jan2011:00:59:59,
	"Hour",
	"start"
);
Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).
.