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

Oracle does not store the dates as: "DD/MON/YY" it stores dates in an internal binary format. I think the OCI manual goes into depth on this. You can also use dump to look at the internal date structure, but I don't really think of it as something one needs to know. What I think you are confusing is the string picture of the date with what the date is stored as. Sure in SQLPlus if you select a date it may very well come back looking like "DD/MON/YY" , but that is predicated on the nls_date format. It would be poor programming practice to rely on that format. Instead if you need dates as a string explicitly ask for them in the format you want. (preferably with 4 digit years) I prefer to keep dates as dates and treat them as an object not as a string.
Jim
"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 - 17:16:45 CEST