topic Custom Date Format in JSL Script (For Query Builder) in Discussions

Custom Date Format in JSL Script (For Query Builder)

bittnere — Fri, 09 Jun 2023 17:03:43 GMT

Overall Goal: Create a query in Query Builder that filters on a date time column only pulling data from the last 7 days of the current time. I am trying to use a custom expression in Query Builder to do this. Ideally it would just be the following in the custom expression filter but it results in no data pulled.

t1.TimeCol >= format((Today() - InDays(7)), "m/d/y h:m:s")

It appears Query Builder takes a specific date time format for its WHERE statement, 'y-m-d h:m:s' (i.e '2022-07-07 11:13:55'), for which JMP doesn't have a built in format for. Therefore I created a custom date format shown below using this example. In a data table it converts a date time column to the custom format as expected. I am struggling to call the format in jsl code though as I get a "Unknown format: myNamespace:QueryDate" error. Any insight? Thanks!

print(format(Today(), "m/d/y"));
print(format(Today(), "myNamespace:QueryDate"));

Custom format:

Add Custom Functions(
New Custom Function(
"myNamespace",
"QueryDate",
Function( {inputVar},
{Default Local},
Char( Year( inputVar ) ) || "-" || Char( Month( inputVar ) ) || "-" ||
Char( Day( inputVar ) ) || " " ||
Char(Hour(inputVar)) || ":" || Char(Minute(inputVar)) || ":" || Char(Second(inputVar))

),
<<Description(
"Date format for query builder y-d-m h:m:s"
),
<<Custom Format Category( 1 )
)
);

Re: Custom Date Format in JSL Script (For Query Builder)

Georg — Thu, 07 Jul 2022 22:08:41 GMT

If you compare a datetime column at database, you need a datetime value for the comparison, but format returns a string. So this will not work so far as I can see. You would need to convert the datetime string at database to a datetime value.

But for your task you can avoid this problem, by simply taking the current date from the database and subtracting some days, and implement this as a custom expression in the Query Builder,

but the syntax will depend on database.

For ANSI it may look like this:

t1.datetime > addday(now(), -7)

and for Oracle it may look like

t1.datetime > sysdate - 7

Re: Custom Date Format in JSL Script (For Query Builder)

Georg — Fri, 08 Jul 2022 06:13:46 GMT

or see other posts:

Solved: SQL query script problem with date format - JMP User Community

Solved: Interactive date selection and filtering by SQL - JMP User Community