INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ADODB Filter with Date

ADODB Filter with Date

(OP)
In my simple application I am trying to do this:
(Cn is my ADODB.Connection to Oracle)

CODE

Dim recDate As New ADODB.Recordset

strSQL = "SELECT MyDateField " & vbNewLine _
    & " FROM MyTable " & vbNewLine _
    & " ORDER BY MyDateField"

recDate.Open strSQL, Cn

recDate.Filter = "MyDateField = TO_DATE('5/5/2015', 'MM/DD/YYYY')" 

recDate.Filter line is creating an error 3001
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I used Filter many times before and I know it has some problems with NULLs, but how should I use it with Date field? Is there a Filter trick with Date?

Google showed some samples/tricks with DateValue in Access, but I don't have it in Oracle. I even tried to covert the Date to Char (TO_CHAR) but still had the same error.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: ADODB Filter with Date

(OP)
With some trial and error, I re-visited my TO_CHAR approach with something like this:

CODE

Dim recDate As New ADODB.Recordset

strSQL = "SELECT TO_CHAR(MyDateField, 'YYYYMMDD') AS MyDateField " & vbNewLine _
    & " FROM MyTable " & vbNewLine _
    & " ORDER BY MyDateField"

recDate.Open strSQL, Cn

recDate.Filter = "MyDateField = '20150505'" 

So pretty much I converted my Date to a character field in the format of YYYYMMDD (so I can even do < or > comparison), but there must be a better way to deal with Dates and Filter.... ?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: ADODB Filter with Date

The filter runs locally, whilst TO_DATE is an instruction to Oracle.

So, why use a filter? Why not put it into the query? Something like

strSQL = "SELECT TO_CHAR(MyDateField, 'YYYYMMDD') AS MyDateField " & vbNewLine _
& " FROM MyTable " & vbNewLine _
& WHERE MyDateField = TO_DATE('5/5/2015', 'MM/DD/YYYY')
& " ORDER BY MyDateField"

RE: ADODB Filter with Date

Yes, it normally makes more sense to use a WHERE clause, but:

CODE

recDate.Filter = "MyDateField = #5/5/2015#" 

The date literal is always in invariant locale (i.e. Earth Standard) format, i.e. MM/DD/YYYY. If you use string delimiters (') it may work though a bit more slowly as it chugs through coercion.

This is documented at MSDN and in your MSDN Library CD docs.

RE: ADODB Filter with Date

(OP)

Quote (strongm)

So, why use a filter? Why not put it into the query?

Good question. smile
I work on a report presented in Excel. So if I need to show, let's say, 10 years of data, I would need to go 10 X 12 = 120 times to the DB, once for each month. I want to go only once to DB, grab the info, and then filter it as many times as I need to.

"The filter runs locally, whilst TO_DATE is an instruction to Oracle."
recDate.Filter = "MyDateField = #5/5/2015#"

That got me thinking... Yes, Filter is local, TO_DATE is Oracle, but # looks like Access, but I tried it anyway. Seams to be working. smile

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: ADODB Filter with Date

># looks like Access

Well, it's a bit more generic to Microsoft langauages than that. Here's the Backus-Naur definition:

DateLiteral ::= # [ Whitespace+ ] DateOrTime [ Whitespace+ ] #
DateOrTime ::=
DateValue Whitespace+ TimeValue |
DateValue |
TimeValue
DateValue ::=
MonthValue / DayValue / YearValue |
MonthValue – DayValue - YearValue
TimeValue ::=
HourValue : MinuteValue [ : SecondValue ] [ WhiteSpace+ ] [ AMPM ]
MonthValue ::= IntLiteral
DayValue ::= IntLiteral
YearValue ::= IntLiteral
HourValue ::= IntLiteral
MinuteValue ::= IntLiteral
SecondValue ::= IntLiteral
AMPM ::= AM | PM


RE: ADODB Filter with Date

When your program talks to Oracle it does so through many layers of software. The Filter property does not accept pass-through SQL at all, and if any SQL is involved (e.g. a server-side cursor) then ADO will ask the OLEDB Provider below it to generate that SQL and squirt it at the DBMS.

This can result in excessive round-tripping though, so you might want to tweak a few Recordset properties people often allow to just default. CacheSize for example, though that can be ineffective unless you raise the "Maximum Open Rows" extended property (assuming the Oracle Provider you are using even supports it). Then you can run into concurrency issues by locking excessive rows though... so you must have your wits about you.

All bets are off if you are relying on the MSDASQL "adapter" Provider and thunking through that into an ODBC Driver for Oracle. The more layers the more risk of losing fidelity and flexibility, not to mention performance.

If you are using client-side cursors then all of the filtering gets done locally and Oracle never knows about it.

RE: ADODB Filter with Date

(OP)
What I have when connecting to Oracle is:

CODE

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Driver={Microsoft ODBC for Oracle};..."
Cn.CursorLocation = adUseClient
Cn.Open
... 

and I would assume the recordset object 'inherits' the CursorLocation from Connection object, which would make it adUseClient

Thanks to both of you for shading some light on the issue. it is a little 'over my head' but I appreciate it very much.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: ADODB Filter with Date

I wouldn't use that crusty old Desktop ODBC Driver, but if you can live with its performance problems, limitations, and it isn't destroying any data I suppose it saves you installing the proper Oracle Client package.

INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider

I'm not sure why you are using client-side cursors, since they impose numerous limitations. But I guess I wouldn't change it because from what I've found most programmers don't understand what is involved and rely on ancient snippets based on VBScript from the Classic ASP days and probably would have trouble getting their programs to work with server-side cursors. So they just live with the performance problems, high memory consumption, concurrency bottlenecks, and round-tripping overhead on updates - in exchange for a sort of "automatic transmission" that lets them ignore so many things.

If it is working and your users, DBAs, and network guys are not screaming at you I suppose that's ok.

RE: ADODB Filter with Date

(OP)
Users don't know, and don't care, DBA is fine with it as long as it does not create any problems, network guys don't care (app is run from just 2 Citrix servers) and I wish I would make it a lot better/faster/more efficient, but I am not in charge. And the person in charge (and the manager) will not do it. Period.

But I do appreciate your comments.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: ADODB Filter with Date

No complaints here. Nearly all of the VB6 work I get these days is to come in and fix those very sorts of performance and concurrency problems.

So, more contract work for me I suppose. ;)

But these days the shift away from Windows has me doing a lot more work targeting Android and Java servlets. The world has left Microsoft behind.

RE: ADODB Filter with Date

Hmm - whilst it certainly appears to be true (e.g. a quick glance at Gooroo's regular reports on programming languages' salaries and demand) that demand for VB/VBA is down, demand for C# remains high, around about the same as for Java and JavaScript - which would indicate that world hasn't quite left Microsoft behind just yet.

RE: ADODB Filter with Date

Plenty of Cobol jobs yet too.

RE: ADODB Filter with Date

use this string format for date:

m_DateStr as String
m_DateSTr = Format(Now, "yyyy-mm-dd hh:nn:ss")

Wael
VoIP Expert
www.tele-finity.com

RE: ADODB Filter with Date

Not quite sure how that would help

RE: ADODB Filter with Date

I don't really know Oracle at all, but my first question is, on the format string - are the month and day field sizes simply restrictive or requirements?
You have "MM/DD/YYYY" for the date string, but provided "5/5/2015" as the date string (instead of "05/05/2015"). Is it that simple?

RE: ADODB Filter with Date

Andy,

Have been accessing Oracle databases from Excel for some 20 years both simply using MS Query or via VBA using ADO objects.

You could quite simply do a parameter query via MS Query with ZERO VBA code or by using a modest amount of code in less than 5 minutes.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: ADODB Filter with Date

(OP)
I don’t know why this tread is back (thanks to waelaswad)...
I was happy with dilettante’s suggestion – a star for him.

The issue was with ADODB’s Filter with Date, Excel part was just to show one example of how I use the Filter with ADODB.

Thanks Skip – I would use your approach, but in my case I push data from Oracle to Excel using VB6. The way I understand your way is to pull data (by Excel) from Oracle. Would work either way.

Case closed (?)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close