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

  • 0
  • 0
  • 105
  • 0
  • 2

Answers

Hi Rizmaan,

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

Rgds

 

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 {'2019-01-01'} AND {'2019-05-10'}