oracle date timestamp

[mdlaugh1]

I have an Oracle 9i database with Access 2003 front end for reporting. I have a query that selects a prompted date range, but I am missing records. Here's an excerpt from Toad:

DATE
2/2/2007
2/2/2007 09:15:22 AM
2/2/2007 12:22:03 AM
2/2/2007

From the list above, my query will pull record 2 & 3, missing 1 & 4.

My query is missing those records listed in Toad as 2/2/2007. these same records are displayed in the users' front end work order application as 2/2/2007 00:00:00.

Records that are correctly being pulled into the query have a stamp that includes a time > 00:00:00 (ie. 09:15:01 AM).

My query criteria statement is:
>=Format([Enter the Beginning Date],"yyyy-mm-dd") And <=Format([Enter the Ending Date],"yyyy-mm-dd")

I changed my criteria to >Format([Enter the Beginning Date], "yyy-mm-dd") then at the Date prompt I entered 02-01-2007 as Beginning Date and this picked up those records with the 00:00:00 timestamp. However, I don;t want the users to have to do the "date manipulation" to force the start date.

Thanks!

 

[cmmrfrds]

I am curious of what happens when you do this.

>=Format(Format([Enter the Beginning Date],"yyyy-mm-dd"),"yyyy-mm-dd")

 

[mdlaugh1]

Now I am missing the records with the full timestamp of hh:mm:ss AM. And it appears that I am missing a few of the ones without the timestamp, although I am not positive about that.

 

[PHV]

And what about this (SQL code) ?
WHERE CDate(Clng([theDatetime field])) Between CDate([Enter the Beginning Date]) And CDate([Enter the Ending Date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mdlaugh1]

Tried the Where CDate.... Copied and modified your suggestion, changing "theDatetme field" to actual_finish_date

Now getting "Expression is typed incorrectly or is too complex to be evaluated".

here is my query grid for date:

Field line: CDate(CLng([actual_finish_date]))

criteria line: Between CDate([beginning date]) And CDate([ending date])

 

[PHV]

And this ?
Field line: Format([actual_finish_date],'yyyy-mm-dd')

criteria line: Between Format([beginning date],'yyyy-mm-dd') And Format([ending date],'yyyy-mm-dd')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

What ODBC driver are you using to the link the tables. Is it Oracles, Microsofts, or a third party?

What is the default date format on the Oracle server?

If it continues to be a problem, have your Oracle DBA capture the SQL that Access is sending to Oracle and then paste the SQL in here so that we can look at it to see what Access and ODBC are actually doing.

 

[mdlaugh1]

that's what I had in the beginning I believe (see my first post), except i have " and you have ' for the quotes.

Am I missing something from your last suggestion?

We're having trouble with those records that basically do not have a time portion to the date value.... and so far it seems that I can either get just those with 2/2/2007 or skip those and get only 2/2/2007 hh:mm:ss
thanks!

 

[cmmrfrds]

As a test you could try matching Oracles default date format like so and see what happens. If this works then you know it is not mapping the other format correctly.

>=Format([Enter the Beginning Date],"dd-mmm-yy")

you would enter 02-FEB-07

 

[HelsByTheDock]

Have you tried:

cdate(Format([Enter the Beginning Date],"yyyy-mm-dd")) And <=cdate(Format([Enter the Ending Date],"yyyy-mm-dd"))?

As Format converts the input to a string, you should convert the result back to a date. The field line should read similarly - CDate(format([actual_finish_date],"yyyy-mm-dd"))

As long as all the dates are of the same datatype and in the same format (including or excluding time) then theoretically your query should work.

Alternatively, have you considered using a Pass-Through query so that Access does not change the SQL? Search for "Create an SQL-specific query (MDB) " in the Access help for details.

Hope this helps