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;" );
"2016-02-16 15:44:42"
Query( Scalar, "SELECT JMPDATE( CURRENT_TIMESTAMP );" );
3538482531
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.
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
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"
"Month"
"MonthName"
Returns the full name of the month in the current language (use_locale = 1) or English (use_locale = 0).
"Mon", "MMM"
"Day"
"DayName"
"DayOfWeek"
"DayOfYear"
"Quarter"
"Hour"
"Minute"
"Second"
"Date"
"Time"
JMPDATE( SQLite time string )
NOW()
TODAY()
Returns the JMP date-time value of the current moment in local time, which matches the JMP Today() function.