Filter by Date not working
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.