Solved

Oracle Query not returnning record using BETWEEN keyword in a date calculation.

Posted on 2003-10-23
10
2,267 Views
Last Modified: 2013-12-25
HI,
I need experts help. I am using VB 6, WinXP, Oracle 8i. One of my queries from VB, retrive records from oracle DB. The query is a follow:
SELECT EMBDTFLD, EMBARGO FROM  MFS.MAGEMBARGO WHERE MID = 'SOR' AND ((to_date('08/01/2003 12:00:00 AM','MM-DD-YYYY HH:MI:SS PM') BETWEEN DTEMBSTART AND DTEMBSTOP) OR ( to_date('08/01/2003 12:00:00 AM','MM-DD-YYYY HH:MI:SS PM') > DTEMBSTART AND DTEMBSTOP IS NULL))

where DTEMBSTART = 1800-01-01 00:00:00 AND DTEMBSTOP = NULL

Whe I run this query at my development envirnement, one record is returned, which is good. When our client run the program, no records returned (We are using the same Oracle Database). I have oracle 8i driver connecting to Oracle 7 DB. They use Oracle 7 driver. Our client retrieeve records if DTEMBSTART >= year 1900.

Does anyone know of any limitation of oracle with pre-1900 dates?

Thanks for all your help
0
Comment
Question by:gfahd1
  • 5
  • 4
10 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 9610156
Are there any errors returned to the client's machine?
0
 

Author Comment

by:gfahd1
ID: 9613679
leonstryker
No error returned. Just 0 record.

Thanks
0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 25 total points
ID: 9613774
Are you able to verify the SQL statement from the client's machine?  

Please try this, change
WHERE DTEMBSTART = 1800-01-01 00:00:00 AND DTEMBSTOP = NULL

to

WHERE DTEMBSTART = 1800-01-01 00:00:00 AND DTEMBSTOP IS NULL
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gfahd1
ID: 9614016
leonstryker
This Query is in my application, so it is the correct query running on the client machine.
AS you can see in my original query, I am using DTEMBSTOP IS NULL.

Thanks
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9614157
Yes, but queries are normally concantinated in the code and I am just making sure it is exactly the same.

Have you tried moving the code into a Package and executing that?
0
 

Author Comment

by:gfahd1
ID: 9614275
Yes it is the same. I output a log file on the client machine witch shows the exact query as I am running.
Do you think it might have anything to do with an Oracle driver Limitation? Year Base Calculation?

Thanks
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9614320
It is definently a driver issue, but I doubt it has anything to do with "Year Base Calculation".  

You may be able to test it but trying to insert this date into the database from the clients machine, if that is possible.  If that does not work try inserting todays date.  At the very least this test would let you know if the problem is in the driver or with the date.

Leon
0
 
LVL 1

Accepted Solution

by:
keshavap earned 25 total points
ID: 9624913
Hi,

Just remove "AND ((to_date('08/01/2003 12:00:00 AM','MM-DD-YYYY HH:MI:SS PM') BETWEEN DTEMBSTART AND DTEMBSTOP) OR ( to_date('08/01/2003 12:00:00 AM','MM-DD-YYYY HH:MI:SS PM') > DTEMBSTART AND DTEMBSTOP IS NULL))" from your query and try.
0
 

Author Comment

by:gfahd1
ID: 9628511
Hello,
Inserting today's date works fine.
Retrieving any date after year 1900 works fine using any date condition.
Inserting any date works ok.
Retrieving a date before year 1900 with a date condition is a problem.
Retriving all records ok.
Removing Year condition works fine.

So, Client is rebuilding the problem machine.

I will keep you posted on results.

Thanks
0
 

Author Comment

by:gfahd1
ID: 9657823
Hello All,
Rebuilding the machine seem to solve the problem.
Thanks for all your help.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question