Re: VB6, DAO, Oracle and dates....

select trunc(REQUIRED_DATE).... will give you the date without the timestamp.

(also

date1 = format(now-30, 'dd-mmm-yy')
date2 = format(now, 'dd-mmm-yy')

where trunc(required_date) between date1 and date2 (formated in code, of course)
)

Also
Have you tried executing the statement NOT as a querydef?

query = "select trunc(required_date)" 'yadayadayad

set rs = myDb.openrecordset(query)

do until rs.eof...

if this works (which it should) you can code your way around it. Not pretty, of course, but doable.

"Nyami" <nyami_at_hotmail.com> wrote in message news:d5bd14e9.0207290304.791a44c0_at_posting.google.com...
> First of some more background:
> The problem I have is that this application was developed a few years
> ago and I have been given the task of bug fixing and enhancing it.
> Changing ODBC drivers, or any other client configuration is not an
> option, the clients current environment works as is and they are not
> keen to change, although with enough justification may be persuaded.
>
> _at_Jim,
> The reason for converting the date field is because the field in the
> database has a timestamp (ie "13/06/2002 12:45:56"), so I was wanting
> a way to drop the time. Using the between two dates approach would
> work, but would also mean changing a few places in the code.
>
> _at_Eric
> I believe that the application originally used access linked tables,
> but this has now been changed (probably not that well) to go directly
> to Oracle. I am connecting to the database using the Oracle ODBC
> driver installed as part of the client install for 7.3.4.
> I have tested using the dbSQLPassThrough option, but this would appear
> to give me a read only recordset. The DAO library I am using is
> "Microsoft DAO 2.5/3.5 Compatibility Library", which I am not overly
> keen on changing, but if this would fix this problem might be an
> option we will have to use.
>
> The database connection is made in the following code:
> Set myDB = OpenDatabase(ODBCDSN, False, False, "ODBC;DATABASE=" +
> ODBCDatabase + ";UID=" + ODBCUser + ";PWD=" + odbcpassword + ";DSN=" +
> ODBCDSN)
>
> Where myDB is of type database the variables are set from values in an
> ini file.
>
> The queries are stored in querydef's like this:
> Set qdMyQuery = myDB.CreateQueryDef("", _
> "PARAMETERS P1 NUMERIC, P2 TEXT , P3 DATE; SELECT * FROM mytable
> WHERE PERSON_ID = P1 AND COURSE_CODE = P2 AND REQUIRED_DATE = P3")
>
> and recordset is populated like this:
> Set qdThisQuery = DBLib.qdMyQuery
> qdPersonCourse(1) = CourseCode
> qdPersonCourse(2) = RequiredDate
> Set rsPersonCourse = qdPersonCourse.OpenRecordset
>
> I have decided to go for using a view, but any pointers to get working
> another way would be great. I also read somewhere the oracle holds
> dates in the "DD/MON/YY" format, should I ideally use this format? and
> has anyone got some pointers to make handling dates fool proof?
>
> Cheers for the help
>
> Nyami
Received on Mon Jul 29 2002 - 14:51:17 CEST