gfahd1
asked on
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
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
leonstryker
Are there any errors returned to the client's machine?
ASKER
leonstryker
No error returned. Just 0 record.
Thanks
No error returned. Just 0 record.
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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?
Have you tried moving the code into a Package and executing that?
ASKER
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
Do you think it might have anything to do with an Oracle driver Limitation? Year Base Calculation?
Thanks
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Hello All,
Rebuilding the machine seem to solve the problem.
Thanks for all your help.
Rebuilding the machine seem to solve the problem.
Thanks for all your help.