asked on

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.

Mark Bullock

What data type is FXDH.VDATE?
If it's timestamp, you can use to_timestamp instead of to_date

slightwv (䄆 Netminder)

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

f19l

ASKER

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.

f19l

ASKER

Actually, it seems to work in one fine but not the other.

slightwv (䄆 Netminder)

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.

Mark Bullock

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.

f19l

ASKER

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.

slightwv (䄆 Netminder)

Try with this change:
Dim st_date as String
Dim end_date as String

f19l

ASKER

Tried that but getting Type mismatch

slightwv (䄆 Netminder)

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

f19l

ASKER

Tried that but got type mismatch

slightwv (䄆 Netminder)

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.

f19l

ASKER

Still getting the same type mismatch error message.

slightwv (䄆 Netminder)

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.

ASKER CERTIFIED SOLUTION

f19l

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

f19l

ASKER

Could not find appropriate solution that worked for me.