?
Solved

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

Posted on 2003-10-23
10
Medium Priority
?
2,275 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 75 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 75 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

800 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