Solved

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

Posted on 2003-10-23
10
2,265 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
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

770 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