Written by

Software Developer at J2 Interactive

Question Rizmaan Marikar · May 14, 2019

Filter by Date not working

Hi All,

I have two tables LB_TestSet and LB_Transfer

LB_Transfer has a list if TestSet row IDs, so i am doing the following join

SELECT 
LBTS_RowID,
,LBTS_CollectedDate
FROM SQLUser.LB_TestSet 
JOIN SQLUser.LB_Transfer ON ($LISTBUILD(LBTS_RowID) %INLIST LBTR_TestSetList)
WHERE LBTS_CollectedDate BETWEEN '2019-01-01' AND  '2019-05-10'

Without the date filter the query returns data, but if i add the date filter no data is returned

I also tried using %INTERNAL, %EXTERNAL TO_DATE('2009-01-01','YYYY-MM-DD')

if i filter by RowID for example the filters work.(this is to make sure that the where clause is working)

Only the date filter fails.

Any Suggestions

Discussion (2)1

Add reply

Comments

Fernando Telesca · May 20, 2019

Hi Rizmaan,

If you are using System Management Portal to run it, you need to select "ODBC Mode".

Rgds

0 0

Vitaliy Serdtsev · May 21, 2019

Date and Time Constructs

Try this:

SELECT 
LBTS_RowID
,LBTS_CollectedDate
FROM SQLUser.LB_TestSet 
JOIN SQLUser.LB_Transfer ON ($LISTBUILD(LBTS_RowID) %INLIST LBTR_TestSetList)
WHERE LBTS_CollectedDate BETWEEN {d '2019-01-01'} AND {d '2019-05-10'}

0 0