[SOLVED]Date condition working as inequality but not as equality

I have a report that lists logged events by date, and I want to restrict it to events of the previous day. The date filter condition is causing me trouble:

Writing it as
WHERE LOGDATE GE DATEADD('&YYMD', 'D', -1)
works as expected, returning all records that have a log date of yesterday or today. The LOGDATE field is included in the output and I verified that records exist for both dates.

However, when I change it to select yesterday's records only,
WHERE LOGDATE EQ DATEADD('&YYMD', 'D', -1)
I get an empty report.

The LOGDATE is a date, not a timestamp. The master file describes it as
FIELDNAME=LOGDATE, ALIAS=LOGDATE, USAGE=YYMD, ACTUAL=DATE, $

Any ideas why no results are returned when the condition looks for an exact date?

This message has been edited. Last edited by: <Emily McAllister>,

WebFOCUS 7.7.03
Windows 7 64-bit

Posts: 17 | Registered: February 23, 2016

Francis Mariani

Expert

posted

Hide Post

Depending on the DBMS the table is in, that date column may actually be a date-time column.

I'd try the following to see if it works:

WHERE LOGDATE GE DATEADD('&YYMD', 'D', -1) AND LOGDATE LT '&YYMD';

Also, I wouldn't embed a WebFOCUS function within a WHERE statement - I believe the function call will either get passed to the DBMS for each row in the table, or cause WebFOCUS to not generate efficient SQL. Try turning SQL traces on to see what happens.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

DomR

Member

posted

Hide Post

Thanks so much Francis, that was it. I cannot see what goes on in the DBMS but restricting the date from both sides worked so LOGDATE must be a datetime column after all.

Thanks for your hint on efficiency as well.

WebFOCUS 7.7.03
Windows 7 64-bit

Posts: 17 | Registered: February 23, 2016

Ignored post by DomR posted

Show Post

Francis Mariani

Expert

posted

Hide Post

I'm happy that worked out.

I would use Dialogue Manager to determine yesterday's date, unfortunately calling DATEADD in Dialogue Manager isn't fun - you have to first convert the string variable &YYMD to a date, then perform the date function, then convert the date back to a string:

-SET &DT_YESTERDAY = DATECVT((DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -1)), YYMD, I8YYMD);

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

DomR

Member

posted

Hide Post

That works nicely. Thanks.

WebFOCUS 7.7.03
Windows 7 64-bit

Posts: 17 | Registered: February 23, 2016

Ignored post by DomR posted

Show Post