Query Builder filtering on date stored in Varchar(20)

HansD — Fri, 09 Jun 2023 18:09:31 GMT

Query Builder - date filter.

SQL Server

JMP version 16.2

In the database (SQL Server) I get the Date field called RETURNDATE in ‘varchar20’ – I would like to use the Calendar functionality in the filter to select my dates

I tried to create a calculated column (Calc1) and specify CAST([RETURNDATE] as date), which he doesn’t like.

Using SQL outside Query Builder

SELECT TOP (1000) [LABJOBNO]

,[DESPATCHNO]

,CAST([RETURNDATE] as date) as CRETURNDATE

…

No problem

Using

Open Database( in JMP JSL with

WHERE ( CONVERT(DATE, DESPATCHRETURN.RETURNDATE, 106) between '01/01/2019' AND '12/31/2019')

Also no problem

Query builder doesn’t allow the entering of the quoted date string in the filter

Any advice?

Hans

topic Query Builder filtering on date stored in Varchar(20) in Discussions

Query Builder filtering on date stored in Varchar(20)