For the latest version of JMP Help, visit JMP.com/help.


Scripting Guide > Types of Data > Date-Time Functions and Formats > Change Date-Time Values in Data Tables
Publication date: 04/21/2023

Change Date-Time Values in Data Tables

In data tables, JMP can accept the input of date-time values in one format (the input format), store them internally as the number of seconds since the base date, and display them in a different date-time format. The Informat() and Format() functions give you this control.

Informat() takes a string date-time value, defines the date format used in that string, and returns the date in ddMonyyyy format.

Informat( "19May2011 11:37:52 AM", "ddMonyyyy h:m:s" );

19May2011:11:37:52

Format() takes the number of seconds since the base date (or a date-time function that returns that number) and returns the date in the specified format.

Format( 3388649872, "ddMonyyyy h:m:s" );

"19May2011 11:37:52 AM"

Format( Today(), "ddMonyyyy h:m:s" );

"19May2011 11:37:52 AM"

Suppose that you are entering dates into a column using the d/m/y h:m format, but you want to see the dates in the m/d/y format. Input Format defines the input format, and Format defines the display format. For example,

New Table( "Widget Assembly",
	Add Rows( 1 ),
	New Column( "Date",
		Numeric,
		"Continuous",
		Format( "m/d/y" ),
		Input Format( "d/m/y h:m" ),
		Set Values( [3126917100] )
	)
);

The Format and Input Format values are shown in the data table’s column properties (Figure 6.3). Note that when you click in the cell to edit it, the date-time value appears in the input format. When you edit the value, or add a new value, the format specified in the data table column Format list is used to display the value.

Figure 6.3 Example of Date-Time Display and Input Values 

Example of Date-Time Display and Input Values

Notes:

In a script that converts a column from character to numeric, specify Format() and Informat() to prevent missing values. See Convert Character Dates to Numeric Dates.

The date-separator character on your computer might differ from the forward slash (/) character shown in the Scripting Guide.

You can enter time values in 24-hour format (military time) or with AM or PM designators.

Table 6.4 describes the formats used as arguments in date-time functions or as data table formats. You can also use the formats for the format argument to a Format message to a data column. See Set or Get Formats.

For descriptions of specific date-time functions, see Date and Time Functions in the JSL Syntax Reference.

Notes:

Date-time formats in which a colon or T separate the date and time always use a 24-hour format. Other formats use your computer’s regional settings.

If the format is unknown, an error is written to the log.

Table 6.4 Date-Time Formats

Type

Format argument

Example

Date only

"m/d/y"

"01/02/1999"

"mmddyyyy"

"01021999"

"m/y"

"01/1999"

"d/m/y"

"02/01/1999"

"ddmmyyyy"

"02011999"

"ddMonyyyy"

"02Jan1999"

"Monddyyyy"

"Jan021999"

"y/m/d"

"1999/01/02"

"yyyymmdd"

"19990102"

"yyyy-mm-dd"

“1999-01-02“

"yyyyQq"

1999Q1

Date and time

"m/d/y h:m"

"01/02/1999 13:01"

"01/02/1999 1:01 PM"

"m/d/y h:m:s"

"01/02/1999 13:01:55"

"01/02/1999 1:01:55 PM"

"d/m/y h:m"

"02/01/1999 13:01"

"02/01/1999 1:01 PM"

"d/m/y h:m:s"

"02/01/1999 13:01:55"

"02/01/1999 1:01:55 PM"

"y/m/d h:m"

‘1999/01/02 13:01’

‘1999/01/02 1:01 PM’

"y/m/d h:m:s"

‘1999/01/02 13:01:02

‘1999/01/02 1:01:02 PM’

"ddMonyyyy h:m"

"02Jan1999 13:01"

"02Jan1999 1:01 PM"

"ddMonyyyy h:m:s"

"02Jan1999 13:01:02"

"02Jan1999 1:01:02 PM"

"ddMonyyyy:h:m"

"02Jan1999:13:01"

"02Jan1999:1:01 PM"

"ddMonyyyy:h:m:s"

"02Jan1999:13:01:02"

"02Jan1999:1:01:02 PM"

"Monddyyyy h:m"

"Jan021999 13:01"

"Jan021999 1:01 PM"

"Monddyyyy h:m:s"

"Jan021999 13:01:02"

"Jan021999 1:01:02 PM"

Day number and time

":day:hr:m"

"34700:13:01"

":33:001:01 PM"

":day:hr:m:s"

"34700:13:01:02"

":33:001:01:02 PM"

"h:m:s"

"13:01:02"

"01:01:02 PM"

"h:m"

"13:01"

"01:02 PM"

"yyyy-mm-ddThh:mm"

1999-01-02T13:01

"yyyy-mm-ddThh:mm:ss"

1999-01-02T13:01:02

Duration

":day:hr:m"

“52:03:01”

reads fifty-two days, three hours, and one minute

":day:hr:m:s"

“52:03:01:30”

reads fifty-two days, three hours, one minute, and thirty seconds

"hr:m"

“17:37”

reads seventeen hours and thirty-seven minutes

"hr:m:s"

“17:37:04”

reads seventeen hours, thirty-seven minutes, and 4 seconds

"min:s"

“37:04”

reads thirty-seven minutes and 4 seconds

Note: The following formats display the date-time according to your computer’s regional settings. They are available only for the display of dates, not for date input in a data table. Examples are shown for the United States locale.

Abbreviated date

"Date Abbrev"

(Display only) “01/02/1999"

Long date

"Date Long"

(Display only) "Saturday, January 02, 1999"

Locale date

“Locale Date”

(Display only) “01/02/1999"

Locale date and time

“Locale Date Time h:m”

(Display only) “01/02/1999 13:01“ or “01/02/1999 01:01 PM“

“Locale Date Time h:m:s”

(Display only) “01/02/1999 13:01:02“ or “01/02/1999 01:01:02 PM“

Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).