Program with Date-Time Functions

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

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

As Date( 50000 );

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 ) );

144

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 );

25

• 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 ) ;

24

• 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.

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

25

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(

// returns a missing value for the first row

			If( Row() == 1,

				.,

				(: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 (macOS) 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.

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 Types of Data.