Need help with select where date.

[#1]

I am creating a subset where I am using Select Where :Login Date == 01/18/2019;  I cannot get it to work.  I also tried opening the subset and running the query builder.  I can add all of the fields and then filter by login date.  The login dates show in the filter.  If I choose one of the dates and hit update, no records show.  Below is my code and I have attached the screen shot of the query builder.  Any help would be appreciated.

Regards,

Wayne

[#2]

Jim,

I agree.  Attached is a sample table.

Regards,

Wayne

[#3]

Jim,

That is the excel that I am importing.  After importing I get the following table.  See attached.

Wayne

[#4]

Jim,

The sample JMP table :Login Date is character format.  I believe the AlstartDT is numeric and that is why I am having the issue?  Do you believe this is the issue?  If so, what do you recommend?

Regards,

Wayne

[#5 · Accepted solution]

The issue is that you have :login Date as a character column, and it contains the time data along with the date data.  So when you compare it to ALstartDT, it is having issues.  I suggest that you uncomment the line where you attempted to change the :Login Date to a numeric with a format of "m/d/y".  It should slightly changed to look like

dt6:Login Date << data type(numeric) << modeling type(continuous)<< format( "m/d/y");

and then change your select where lines to

dt6 << Select where ((Contains(MyList, :Site ID)) & :Analysis == "G4");
dt6 << Select where (:Login Date >= informat(ALstartDT,"mm/dd/yyyy"),current selection("extend"));

[#6]

Jim,

No errors, but it does not subset by the ALstartDT date pulled in from the date textbox.  My file contains rows with login dates of 1/2/2019 through 1/24/2019.  My ALstartDT is 01/18/2019.

Regards,

Wayne

[#7]

Jim, 

I noticed in the debug tool that it states that ALstartDT is a string not a number.  Hopefully this helps.

Regards,

Wayne

[#8]

Jim,

I finally figured it out. I took your solution then got rid of the extend and added it back to my original select where. For some reason the extend did not work.
Thank you for taking all of the time helping me work this out. Very much appreciated. Without all of the experts on this forum, folks learning like myself would not be able to complete their projects.

Regards,

Wayne

[#9]

Note that you can use literal date values (constants) directly without the need to convert between character strings and numeric values. The form is DDMmmYYYY:HH:MM:SS.S. (You only use as much of this form as necessary, omitting from the right side.) So, for example, I could directly enter the correct numeric value for 8 AM today as:

29Jan2019:08:00

The script editor acknowledges this value as a number with the color coding if you have the feature enabled.

[#10]

To expand on @Mark_Bailey's response here is an example showing the date constant in use.

dt = open("$SAMPLE_DATA\TechStock.jmp");
dt << select where(Date <= 05Jan2001);

For more tips on dates see Using dates, times, datetimes and durations in JMP.