I have a procedure that converts a date from 'YYMD' to 'QY' format to report at the Quarter and Year level. This is done by using a defined field and it works perfectly. However, whenever I try to filter this Quarter/Year date field, my report will not produce any results. The defined field looks like:
QUARTERYEAR/QY=PLANEXECUTEDDATE;
(PLANEXECUTEDDATE is YYMD)

I've also tried this which didn't change the results:
DATECVT(PLANEXECUTEDDATE, 'YYMD', 'QY');

I have tried multiple variations of Quarter/Year to try to get the filter to work properly with no success. Examples I've tried are: 414 (fourth quarter 2014, which is what the GUI shows as a valid value), Q4 14 (which is how it displays in the report), Q414, and other combinations.

Any ideas what I might be missing? Is there some defined field issue I am overlooking?

Thanks

This message has been edited. Last edited by: capples,

This seems to work (using a demo table):

DEFINE FILE EMPLOYEE
HIRE_QY/QY = HIRE_DATE
END

TABLE FILE EMPLOYEE
PRINT
EMP_ID
BY HIRE_QY
BY HIRE_DATE

WHERE HIRE_QY = 'Q1 82';

END

So after inspecting that demo table, the Hire_Date field is I6YMD, I converted my date field to I6YMD and now it allows me to filter properly. I'm not really sure why this works to be honest, but I'll go with it.

Thanks Francis.

That's odd, because it seems to work with YYMD dates. Perhaps it works because the demo is a FOCUS DB and not a RDBMS table.

DEFINE FILE EMPLOYEE
HIRE_DATE1_YYMD/YYMD = HIRE_DATE;
HIRE_DATE_YYMD/YYMD = HIRE_DATE1_YYMD;
HIRE_QY/QY = HIRE_DATE_YYMD;
END

TABLE FILE EMPLOYEE
SUM
EMP_ID
BY EMP_ID NOPRINT
-*BY HIRE_QY
-*BY HIRE_DATE
-*BY HIRE_DATE_YYMD

WHERE HIRE_QY = 'Q1 82';

END