[SOLVED] 7.6.11 hotfix 8 installed - now date filter not working
Hi all, We've been on 7.6.11 hotfix 5 for ages and yesterday installed hotfix 8. Today a couple of reports aren't working that search by a date range. Simple report using just one foc file. The data is built into the foc files from txt files. The txt master file:
The report html happily shows the date to allow the selection of from and to date. Under hotfix 8 no data is returned (even when same from and to are used) . Under hotfix 5 report works fine. Actual date in txt file is format dd/mm/yy
fex is doing a simple WHERE DATE GE &D1; WHERE DATE LE &D2;
Help!This message has been edited. Last edited by: Darryl_uk,
7.7.05 Windows.
January 09, 2013, 12:36 PM
Dan Satchell
Try putting quotes around your date variables in the WHERE clauses. Otherwise WF may think they are numbers instead of dates.
WHERE DATE GE '&D1';
WHERE DATE LE '&D2';
WebFOCUS 7.7.05
January 09, 2013, 03:32 PM
Waz
What is the format of &D1 and &D2 ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
January 10, 2013, 03:03 AM
Darryl_uk
Dan - thanks, that makes sense. So easy I didn't spot it. The report should never have worked since it was written (6 years ago)..!
Waz - A good question that I have never got my head around. the data arrives in the txt file as '99/99/99', is defined as 'dmy', is loaded into the foc as 'dmy', is picked up and displayed on the html screen as 99/99/99 so arrives from the html into the fex as..? I go put it into debug and look see.
Many thanks gents - I can't get hotfix 8 back up until tomorrow when the chap who does such things is back in work. But once done I'll update and close.
7.7.05 Windows.
January 10, 2013, 03:23 AM
Darryl_uk
The date that is displayed as 13/03/08 on the screen comes into the program as: WHERE DATE GE 130308; so yes I can see where the lack of quotes may cause a direct numeric comparison rather than a date comparison. Quotes now added in and will see what happens tomorrow under service pack 8.
Thanks again
7.7.05 Windows.
January 10, 2013, 03:38 AM
Wep5622
I suspect that in HF8 you'll find that the date is returned as '13/03/08' instead.
Somewhere around HF5 or 6 the date from date-fields accidentally got its delimiters stripped when passed on to the procedure (which, I must add, was actually rather convenient for us as our dates are stored as integers).
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
January 11, 2013, 05:21 AM
Darryl_uk
Wep, Waz & Dan,
All correct and working as per your info. With Hotfix 8 the slashes stay in the date passed back in by the html so the quotes around the parameter fixed the where statement.