How do I choose a date range in Oracle SQL?

Hello,

I have a spreadsheet that I use to connect directly to an Oracle db. The file contains some code that updates a linked table. Part of the operation to update involves selecting a date range but when I try to do it I get a type mismatch error message. Please see below.

Sub Macro1()
Dim st_date, end_date
Dim ws As Worksheet, r As Range
end_date = Format(Range("date"), "yyyy-mm-dd")
st_date = Format(Range("prevdate"), "yyyy-mm-dd")
    Range("CashRec_GRI").Cells(55, 2).Select
    With Selection.ListObject.QueryTable
    .Connection = (....) // I have already tested the connection and it works fine.
    .CommandText = Array("SELECT FXDH.BR, FXDH.TRAD, FXDH.VDATE, FXDH.CCY, FXDH.CCYAMT, FXDH.CTRCCY, FXDH.CTRAMT FROM OPICSPLUSDB.FXDH FXDH WHERE (FXDH.VDATE BETWEEN TO_DATE('2015-08-01','YYYY-MM-DD') AND TO_DATE('2015-08-31','YYYY-MM-DD')) AND (FXDH.TRAD In ('IGRI','ISIN'))")
    .Refresh BackgroundQuery:=False
   
    End With

Please could someone help?

Thanks.
f19lAsked:
Who is Participating?
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.

Mark BullockQA Engineer IIICommented:
What data type is FXDH.VDATE?
If it's timestamp, you can use to_timestamp instead of to_date
0
slightwv (䄆 Netminder) Commented:
The query looks fine to me.  My guess is it has something to to when the data comes back.

You can confirm this by running the query in SQL Developer or sqlplus and remove Excel from the equation.

>>If it's timestamp, you can use to_timestamp instead of to_date

BETWEEN with dates will work on a timestamp column.

drop table tab1 purge;
create table tab1(col1 timestamp);
insert into tab1 values(systimestamp);
commit;

select * from tab1 where col1 between to_date('01/01/2001','MM/DD/YYYY') and to_date('01/01/2099','MM/DD/YYYY');

Open in new window

0
f19lAuthor Commented:
Thanks all. It turns out that after removing the "FXDH." from the command text code line I was able to get the spreadsheet to work and pull in the relevant data with other changes being made.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

f19lAuthor Commented:
Actually, it seems to work in one fine but not the other.
0
slightwv (䄆 Netminder) Commented:
In your example FXDH is the schema name for the table/view.  If you remove it, you might not be looking at the table you think you are looking at.

As for it not working with another, again, it might be looking at a different table.

Most of the time access is controlled through synonyms so you don't have to provide the specific schema names.

You'll need to work with the DBAs or System Analysts to figure out what data is supposed to come from what schema and what tables in what schema.
0
Mark BullockQA Engineer IIICommented:
In the spreadsheet which doesn't work, what username do you use to connect to the database? Perhaps it's a different user than the other spreadsheet file.
0
f19lAuthor Commented:
I have attached the code below

Dim st_date, end_date
Dim ws As Worksheet, r As Range
end_date = Format(Range("currentdate").Value, "yyyy-mm-dd")
st_date = Format(Range("prevdate").Value, "yyyy-mm-dd")

Cells(55, 2).Select
 With Selection.ListObject.QueryTable
    .Connection = ...using the same connection as on my other spreadsheet that works fine
    .CommandText = Array("SELECT BR, TRAD, VDATE, CCY, CCYAMT, CTRCCY, CTRAMT FROM OPICSPLUSDB.FXDH FXDH WHERE (VDATE BETWEEN to_date('" & st_date & "','yyyy-mm-dd') AND to_date('" & end_date & "','yyyy-mm-dd')) AND (TRAD in ('IGRI','ISIN','ICAD','ICHF','IEUR','IGBP','IJPY','IUSD','INOK','ISEK','IDKK'))")
    .Refresh BackgroundQuery:=False
End With

When I run it I get a type mismatch error.

When I exclude the between date part and just use VDATE>(to_date('" & st_date & "','yyyy-mm-dd',')) then it work.
0
slightwv (䄆 Netminder) Commented:
Try with this change:
Dim st_date as String
Dim end_date as String
0
f19lAuthor Commented:
Tried that but getting Type mismatch
0
slightwv (䄆 Netminder) Commented:
I'm trying to determine if it is Excel or Oracle generating the error.

Try after changing this:
OPICSPLUSDB.FXDH FXDH WHERE (VDATE BETWEEN to_date('" & st_date & "','yyyy-mm-dd') AND to_date('" & end_date & "','yyyy-mm-dd')) AND (TRAD in

to this:
OPICSPLUSDB.FXDH FXDH WHERE (VDATE BETWEEN to_date('" & "2015-01-01" & "','yyyy-mm-dd') AND to_date('" & "2015-01-01" & "','yyyy-mm-dd')) AND (TRAD in
0
f19lAuthor Commented:
Tried that but got type mismatch
0
slightwv (䄆 Netminder) Commented:
Try this:
...
OPICSPLUSDB.FXDH FXDH WHERE (VDATE BETWEEN to_date('2015-01-01','yyyy-mm-dd') AND to_date('2015-01-01','yyyy-mm-dd')) AND (TRAD in
...

Make sure the dates you use will return data.

I'm thinking there is an implicit data type conversion error happening (Oracle or Excel is 'guessing' at the data type but there is 'bad' data that it cannot convert).  When it 'works' it isn't returning the 'bad' data.
0
f19lAuthor Commented:
Still getting the same type mismatch error message.
0
slightwv (䄆 Netminder) Commented:
There is no problem with the SQL that I can see.  I don't think the error is coming from Oracle.

Check the data and cell formats where the data is eventually going.  My money is on the implicit data conversion I mentioned above.  Something like Oracle is returning a string and the cell in Excel is a number.

>>When I exclude the between date part and just use VDATE>(to_date('" & st_date & "','yyyy-mm-dd',')) then it work.

Using this and the BETWEEN, is it returning the exact same rows?  If not, the rows not being returned will likely be the ones causing the error.

At this point, I would focus on the data.
0
f19lAuthor Commented:
I have tried to work on this but still I am getting nowhere. As such rather than waste more time I have found a work around that gives me the results I expected. Basically I do not try to filter for data between specified dates but choose everything beyond a certain date and then delete row by row any information then does not meet my criteria. Thanks for all your help anyway but I will now be deleting this question.
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
f19lAuthor Commented:
Could not find appropriate solution that worked for me.
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
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.