Date-Time SQL Functions

Using date-time functions in JMP queries is complicated by the fact that the SQL engine that handles JMP queries (SQLite) uses different formats for storing dates than JMP does. SQLite stores date-times as strings. However, JMP stores date-times as the number of seconds since January 1, 1904. When you have columns in your table that contain date-times, the conversions are handled automatically. However, when you use functions that return date-times, you might need to let JMP know when a conversion is required.

Consider the CURRENT_TIMESTAMP function. CURRENT_TIMESTAMP is a built-in SQLite function that returns the current UTC/GMT time stamp as a SQLite time string:

Query( Scalar, "SELECT CURRENT_TIMESTAMP;" );

returns:

"2016-02-16 15:44:42"

The string could perhaps be parsed as a date to return it as a JMP date. To prevent the need to do so, wrap the CURRENT_TIMESTAMP function in the JMPDATE() function:

Query( Scalar, "SELECT JMPDATE( CURRENT_TIMESTAMP );" );

returns:

3538482531

The string is an unformatted JMP date. However, if you pass a SQLite time string to another SQL date-time function, you do not need to use JMPDate(); the value will be converted to a JMP date automatically. Here is an example:

Query( Scalar, "SELECT EXTRACT(’YEAR’, CURRENT_TIMESTAMP);" );

Using native SQLite date-time functions (date(), time(), datetime(), julianday(), strftime()) in JMP queries is not recommended because JMP date-time values are not compatible with those functions.


Date-Time Function	Naive SQLite	Description
CURRENT_DATE	Yes	Returns the current date (UTC/GMT) as a SQLite time string.
CURRENT_TIME	Yes	Returns the current time (UTC/GMT) as a SQLite time string.
CURRENT_TIMESTAMP	Yes	Returns the current date and time (UTC/GMT) as a SQLite time string.
DATEDIFF( date1, date2, interval, <alignment = “Start”> )		Computes the difference between two dates in units specified by interval, based on alignment. This function works the same as the Date Difference() JSL function. Valid values for interval are: “Year”, “Quarter”, “Month”, “Week”, “Day”, “Hour”, “Minute” and “Second”. Valid values for alignment are “Start”, “Actual” and “Fractional”. If alignment is not specified, “Start” is used.
EXTRACT( datepart, datetime, <use_locale = 1> )		Extracts a specific part of a date or date-time value. Datetime is a JMP date-time value or a SQLite time string. Use_locale is optional and applies only to date name parts such as "MonthName" and "DayName" and determines whether values from the current language or English are returned. The following values of datepart are supported:
		"Year"	Returns the year as a number.
		"Month"	Returns the numeric month (1-12).
		"MonthName"	Returns the full name of the month in the current language (use_locale = 1) or English (use_locale = 0).
		"Mon", "MMM"	Returns the abbreviated name of the month.
		"Day"	Returns the day of the month (1-31).
		"DayName"	Returns the name of the day of the week.
		"DayOfWeek"	Returns the numeric day of the week (1-7).
		"DayOfYear"	Returns the numeric day of the year (1-366).
		"Quarter"	Returns the numeric quarter (1-4).
		"Hour"	Returns the hour (0-23).
		"Minute"	Returns the minute (0-59).
		"Second"	Returns the seconds, including any fractional part.
		"Date"	Returns just the date portion of a date-time value as a JMP date-time value.
		"Time"	Returns just the time portion of a date-time value as a JMP date-time value.
JMPDATE( SQLite time string )		Converts a SQLite time string to the equivalent JMP date-time value.
NOW()		A synonym for TODAY().
TODAY()		Returns the JMP date-time value of the current moment in local time, which matches the JMP Today() function.