Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Glyn Derby

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...