Solved

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

Posted on 2003-10-23
10
2,273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

695 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