I am querying oracle tables. The mfds date fields are oracle...

[Glyn Derby]

I am querying oracle tables. The mfds date fields are oracle date-time format. In order to extract data from a particular date range I am using a series of defines to create a defined field with format YYMD. Then I simply query using the defined YYMD format field and a standard date. This has worked on numerous oracle tables for over a decade. From around November 2020 we are getting intermittent SQL Cursor failures and fail to extract data. These types of queries appear to extract all of the data from the oracle table and then apply the defined data filter afterwards. The sql query at the database end has no date element. (so we think we have a space issue).

I would like to create a date range query against an oracle table without having to define a YYMD format date field first. What is the command/syntax for querying an oracle table to extract data from within a date range

[Martin Yergeau]

Does this is working:

-*-*Assuming that &DateFrom and &DateTo parameter are provided from calendar control selection

-SET &DateFrom = IF &DateFrom CONTAINS '/' THEN DATECVT(EDIT(&DateFrom, '9999$99$99'), 'A8YYMD', 'I8YYMD') ELSE DATECVT(&DateFrom, 'A8YYMD', 'I8YYMD');

-SET &DateTo = IF &DateTo CONTAINS '/' THEN DATECVT(EDIT(&DateTo, '9999$99$99'), 'A8YYMD', 'I8YYMD') ELSE DATECVT(&DateTo, 'A8YYMD', 'I8YYMD');

-SET &DateTo = AYMD(&DateTo, 1, 'I8YYMD');

 

TABLE FILE ...

...

WHERE dateField GE &DateFrom) AND dateField LT &DateTo);

-*-*Or this option

-*WHERE dateField GE DT(&DateFrom) AND dateField LT DT(&DateTo);

 

END

But there is so many ways to work with datesa whole IBI book exist just to talk about dates usage/manipulation

[Glyn Derby]

Thanks MartinY, but no, I get

(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT

[NYCBabak .]

Are these date fields in your MFDs assigned HYYMDs FORMAT and ACTUALs If so, skip the DEFINE and just edit the MFD removing the s from HYYMD and use the date field for your WHERE tests instead of redefining them.

[Manoj Chaurasia]

To add to what NYCBabak has suggested by default when synonyms (MFDs) are created a date timestamp definition will be created for all date fields. If you dont need to use the full date timestamp you can put this line of code in the server profile (edasprof.prf) and it will just create DATE fields when the synonym is created.

ENGINE SQLORA SET DATETIME OFF

[Glyn Derby]

OK Thanks I need to take these items up with my administrators

[Alban Hertroys]

You could just use DTRUNC(..., DAY) in your WHERE-clause, but that depends on whether there is an index on what that call translates to for Oracle.

Its often preferable to modify your (constant) parameters instead, for example using GE DT('&DateFrom 00:00:00') and LT DT('&DateUntil 00:00:00').

[Glyn Derby]

Youre right Alban, this is what I found over the weekend, using this format does the job very simply and the retrieval time from the database is reduced to less than a second. All I have to do is translate the date and time into a variable with the format below. thanks for your help.

WHERE TRANSDATE GT DT(2000/01/01 02:57:25)