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

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
gfahd1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leonstrykerCommented:
Are there any errors returned to the client's machine?
0
gfahd1Author Commented:
leonstryker
No error returned. Just 0 record.

Thanks
0
leonstrykerCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gfahd1Author Commented:
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
leonstrykerCommented:
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
gfahd1Author Commented:
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
leonstrykerCommented:
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
keshavapCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gfahd1Author Commented:
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
gfahd1Author Commented:
Hello All,
Rebuilding the machine seem to solve the problem.
Thanks for all your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.