Hi All.

I have a date coming from an ORACLE database (date time field) and I need to check that this falls within the past 7 days.
I know about defining a variable = &DMY to get todays date, and presumably I could then have another variable = that one -7 for the start of the date range, but I cannot seem to get the formats of this date and my ORACLE date to match so that I can get them to work in the filter.
I do not have access to modify any code so I'm limited to what can be done via the infoassist interface.
Could anyone help please?

Thank you.

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

Lookup the HDATE function

Thanks Tom, the HDATE function was good for converting my Oracle date but I still have a problem.
I defined a field from the Oracle date as

HDATE(db_view.SESSION_CREATION_DATE , 'YYMD')

(called sdate)


Then I defined a field for todays date as

&YYMD (called this field td)

and finally one more as todays date - 7 days using

DATEADD (td , 'D', -7) (called Dminus7)

They all have the format YYMD, but when I try to use these in a filter such as

sdate greater than dminus7

I get the error 'the format of the test value is inconsistent with field format: dminus7'

Sorry if this is something really simple but Infoassist is not something I use a great deal (yet!)

Hi tcox,
Dates have to be "smart dates".
Try this DEFINE code:

  
SDATE/YYMD = HDATE(db_view.SESSION_CREATION_DATE , 'YYMD');
CUR_DT/YYMD = DATECVT(&YYMD, 'I8YYMD', 'YYMD');
CUR_MINUS_7/YYMD = DATEADD(CUR_DT, 'D', -7);

Then, in the code:
WHERE SDATE GE CUR_MINUS_7 AND SDATE LE CUR_DT;

tcox,
This is an excellent resource book addressing WebFOCUS dates, well worth the price; may be available at Summit as well...

1001 Ways to Work with Dates in WebFOCUS

Hi Tom.
Thanks for all your help on this.
The good news is that I did exactly what you suggested and managed to get into the 'managed reporting applet' so that I could change the code, and ran the report from there and it worked perfectly.
I then went back to the Infoassist user interface (which is where we want our users to develop reports) and it still comes up with the same error about inconsistent formats and it will not run.

Just as another idea, I read about Report Assistant which I also managed to get into via the MR-Applet.
I re-created my report in there and ran it.
It worked perfectly again.

So I now have three ways to run this: two of which work fine, but the third doesn't and that's the one I need for the users.

So why is the user interface / interactive version failing?

tcox,
I opened IA and was able to generate the 3 new columns successfully(see image below). You probably need to remove db_view. from SDATE:
SDATE/YYMD = HDATE(SESSION_CREATION_DATE, 'YYMD');

Also, make sure when you enter the DEFINE that the Format is changed to YYMD and there is no semi-colon at the end...
If it still doesn't work, contact IBI...
Good Luck!
Tom

I have closed this case to refer it to IBI.
Currently the report works fine if started from the managed reporting applet.
Thank you for you support and advice.

Tony...

I think you guys are making this too hard. What follows is based on the assumption that if today is Monday, you want data where SESSION_CREATION_DATE is from last Monday through Sunday. I also assume that in the master, SESSION_CREATION_DATE is an H format (date time).

-SET &7DAYS=AYMD(&YYMD,-7,'I8YYMD');
 TABLE FILE XXXX
 PRINT FLDA FLDB ...
    BY ...
 WHERE SQL.TRUNC(SESSION_CREATION_DATE) GE '&7DAYS'
   AND SQL.TRUNC(SESSION_CREATION_DATE) LT '&YYMD'
 END

Another option if you don't have the ability to use the "SQL." prefix for Oracle functions is to do this in the master.

FIELD=SDTTM,SESSION_CREATION_DATE,HYYMDS,HYYMDS,$
FIELD=SDATE,SESSION_CREATION_DATE,YYMD,DATE,$

Then replace SQL.TRUNC(SESSION_CREATION_DATE) with SDATE.

I do this all the time.

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