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"
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 );" );
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.
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() |
A synonym for TODAY().
|
||
TODAY() |
Returns the JMP date-time value of the current moment in local time, which matches the JMP Today() function.
|