Program with Date-Time Functions

Table 6.2 shows functions that convert seconds into date-time values and date-time values into seconds.

Abbrev Date(date)

Returns a string representation for the date supplied. The format is based on your computer’s regional setting. So for the English (United States) locale, the date is formatted like "02/29/2004". Even if you are running JMP in English with a different locale, the locale format is applied.

As Date(expression)

Formats a number or expression so that it shows as a date or duration in a text window. For example, values that represent one year or more are returned as dates.

x = As Date( 8Dec2000 + In Days( 2 ) );

shows as:

10Dec2000

Values that represent less than a year are returned as durations.

As Date( 50000 );

shows as:

:0:13:53:20

Date DMY(day, month, year)

Returns the specified date expressed as the number of seconds since midnight, 1 January 1904. For example, the second Leap Day of the third millennium is DateDMY(29,2,2004), which returns 3160857600.

Date MDY(month, day, year)

Returns the specified date expressed as the number of seconds since midnight, 1 January 1904. For example, the second Leap Day of the third millennium is DateMDY(2,29,2004), which returns 3160857600.

Day Of Week(date)

Returns an integer representation for the day of the week of the date supplied. Weeks are Sunday–Saturday.

Day Of Year(date)

Returns an integer representation for the day of the year of the date supplied.

Day(date)

Returns an integer representation for the day of the month of the date supplied.

Format(date, "format")

Returns the value in the format specified in the second argument. Most typically used for formatting datetime values from a number of seconds to a formatted date. Format choices are those shown in the Column Info dialog box. Also see Table 6.3.

Hour(datetime)

Returns an integer representation for the hour part of the date-time value supplied.

In Days(n)

These functions return the number of seconds per n minutes, hours, days, weeks, or years. Divide by these functions to express an interval in seconds as an interval in other units.

In Hours(n)

In Minutes(n)

In Weeks(n)

In Years(n)

Long Date(date)

Returns a string representation for the specified date. The format is based on your computer’s regional setting. So for the English (United States) locale, the date is formatted like "Sunday, February 29, 2004". Even if you are running JMP in English with a different locale, the locale format is applied.

MDYHMS(date)

Returns a string representation for the date supplied, formatted like "2/29/2004 00:02:20 AM".

Minute(date-time)

Returns an integer representation for the minute part of the date-time value supplied.

Month(date)

Returns an integer representation for the month of the date supplied.

Num(date-time)

Returns an integer representation for the date-time value supplied.

InFormat(string, "format")

Parse Date(string, "format")

Parses a string of a given format and returns datetime value expressed as if surrounded by As Date(), returning the date in ddMonyyyy format.

Second(date-time)

Returns an integer representation for the second part of the date-time value supplied.

Short Date(date)

Returns a string representation for the date supplied, in the format mm/dd/yyyy, regardless of locale (for example, "02/29/2004").

Time Of Day(date)

Returns an integer representation for the time of day of the date-time supplied.

Today()

Returns the current date and time expressed as the number of seconds since midnight, 1 January 1904. No arguments are accepted, but the parentheses are still needed.

Week Of Year(date, <rule_n>)

Returns the week of the year as a date-time value. Three rules determine when the first week of the year begins.

•

With rule 1 (the default), weeks start on Sunday, with the first Sunday of the year being week 2. Week 1 is a partial week or empty.

•

With rule 2, the first Sunday begins with week 1, with previous days being week 0.

•

With rule 3, the ISO-8601 week number is returned. Weeks start on Monday. Week 1 is the first week of the year with four days in that year. It is possible for the first or last three days of the year to belong to the neighboring year’s week number.

Year(date)

Returns an integer representation for the year of the specified date.

Examples of Common Date-Time Functions

You can use any function that returns seconds within a function that returns a date-time.

For example, if today is May 19, 2011 and the time is 11:37:52 AM, Today() returns the number of seconds, and the functions that follow show that number of seconds since the base time in different date-time formats:

Today()

3388649872

Short Date( Today() );

"05/19/2011"

Long Date( Today() );

"Thursday, May 19, 2011"

Abbrev Date( Today() );

"5/19/2011"

MDYHMS( Today() );

"05/19/2011 11:37:52 AM"

The date argument in parentheses can be seconds (or any function that returns seconds), or any date-time literal value. For example, both of the following expressions return the same value:

Long Date( 3388649872 );

Long Date( 19May2011 );

"Thursday, May 19, 2011"

Note: Long Date() and Abbrev Date() values are formatted according to your computer’s regional settings.

Extract Parts of Dates

You can extract parts of date values using the functions Month(), Day(), Year(), Day Of Week(), Day Of Year(), Week Of Year(), Time Of Day, Hour(), Minute(), and Second(), which all return integers. If today is May 24th, 2011, each of the following examples returns the 144th day of the year:

Day of Year( Today() );

Day of Year( 24May2011 );

Day of Year( Date MDY( 5, 24, 2011 ) );

Example

A data table column named Date contains date-time values that are formatted as "m/d/y". You want to create a column that shows only the time. In the following script, the second column’s formula extracts the time of day from the Date value in the first column.

New Table( "Assembly Tests",

	Add Rows( 1 ),

	New Column( "Date",

		Numeric, Continuous,

		Format( "m/d/y" ),

		Set Values( [3389083557] )

),

	New Column( "Time",

		Numeric, Continuous,

		Formula( Format( Time Of Day( :Date ), "h:m:s") )

);

Figure 6.1 shows the result. Note that the time of day does not appear in the Date column, because the Format function applies the “m/d/y” format.

Figure 6.1 Example of Extracting the Time

Rules for Determining the Week of the Year

Week of Year() returns the week of the year as a date-time value. Three rules determine when the first week of the year begins.

•

With rule 1 (the default), weeks start on Sunday, with the first Sunday of the year being week 2. Week 1 is a partial week or empty.

Week Of Year( Date DMY( 19, 6, 2013 ), 1 );

•

With rule 2, the first Sunday begins with week 1, with previous days being week 0.

Week Of Year( Date DMY( 19, 6, 2013), 2 ) ;

•

Week Of Year( Date DMY( 19, 6, 2013 ), 3 );

Arithmetic on Dates

You can perform the usual arithmetic operations with date-time data as with any other numeric data. One option is simple arithmetic, such as subtracting a number from a date-time value.

Another option is writing a formula to perform the arithmetic.

Example

The Date column in your data table shows when a customer uses his credit card to buy gas. You want to know how many days elapse between purchases. The following script creates a Days elapsed column. The formula in that column subtracts the Date value in the current row from that of the previous row.

New Table( "Gas Purchases",

	Add Rows( 3 ),

	New Column( "Date",

		Numeric,

		"Continuous",

		Format( "m/d/y" ),

		Set Values( [3392323200 3393532800 3394828800] )

),

	New Column( "Days elapsed",

		Formula(

			If( Row() == 1,

				., // returns a missing value for the first row

				(:Date[Row()] - :Date[Row() - 1]) / In Days()

);

Figure 6.2 shows the result.

Figure 6.2 Example of Calculating Date-Time Values

Time Intervals

The In Minutes, In Hours, In Days, In Weeks, and In Years functions are used to express time intervals in units rather than seconds. Each of these functions returns the number of seconds associated with a particular period of time. For example, the following expression returns the number of weeks between now and July 4, 2012.

(Date DMY( 04, 07, 2012 ) - Today()) / In Weeks();

-208.225444775132

When the argument for the interval function is empty, JMP counts by 1. You can enter another number to change the count. For example, In Years(10) converts the interval to decades. The following expression returns the number of decades between now and December 31, 2037.

(Date DMY( 31, 12, 2037) - Today() ) / In Years( 10 );

2.18927583529799

Two- and Four-Digit Years

JMP applies its own algorithms for interpreting and displaying datetime strings rather than supporting operating system-specific datetime formats. However, JMP uses the date-time separators selected in the Region and Language control panel (Windows) or the Date & Time preferences (Macintosh) to interpret and display dates.

Two-digit years are interpreted according to the current system clock year and JMP rules. For example, when the year in a script is 11, and you run the script after 1990, the year shows as 2011.

Long Date( 25May11 );

"Wednesday, May 25, 2011"

To avoid ambiguity, enter four-digit years. The following expression returns 1911 (rather than 2011) as indicated:

Long Date( 25May1911 );

"Thursday, May 25, 1911"

Table 6.3 explains how JMP interprets two-digit years.

Table 6.3 How JMP Interprets Two-Digit Years
Two-Digit Year Value	When it is Evaluated	Result	Examples	Result
00–10	before 1990 (on Windows)	19__	enter 5 in year 1979	1905
	before or during 1990 (on Macintosh)	19__	enter 5 in year 1979	1905
	during or after 1990 (on Windows)	20__	enter 5 in year 1991	2005
	after 1990 (on Macintosh)	20__	enter 5 in year 1991	2005
11–89 (on Windows)	any time	current century	enter 13 in year 1988	1913
11–90 (on Macintosh)	any time	current century	enter 13 in year 2024	2013
90–99 (on Windows)	before 2011	19__	enter 99 in year 1999	1999
91–99 (on Macintosh)	during or after 2011	20__	enter 99 in year 2015	2099

Note: JMP always displays four-digit years regardless of the regional settings. If you need to show two-digit years, use character string functions. See the Types of Data topic.