SELECT problem using ADO

Hello,

I have a database with one table (tblTest) which has two columns (id
(int) and ts (date/time)). In this table I want to select all records
between to times using the following query:

SELECT * FROM tblTest WHERE (ts >= #1/1/2007# And ts <= #12/31/2007
23:59:0#);

or

SELECT * FROM tblTest WHERE (ts BETWEEN #1/1/2007# And #12/31/2007
23:59:0#);

This works without problems when entered in Access. But, performing a
query using ADO from a C++ application this gives the following
exception (translated from Swedish).

"The parameter object is badly defined. The supplied information is
inconsistent or incomplete"

If I only use dates instead of date + time then everything works ok.


Does anyone know why this happens?

Re: SELECT problem using ADO

k.renaud@gmail.com wrote in news:1194428413.059945.321970
@o80g2000hse.googlegroups.com:

> Hello,
>
> I have a database with one table (tblTest) which has two columns (id
> (int) and ts (date/time)). In this table I want to select all records
> between to times using the following query:
>
> SELECT * FROM tblTest WHERE (ts >= #1/1/2007# And ts <= #12/31/2007
> 23:59:0#);
>
> or
>
> SELECT * FROM tblTest WHERE (ts BETWEEN #1/1/2007# And #12/31/2007
> 23:59:0#);
>
> This works without problems when entered in Access. But, performing a
> query using ADO from a C++ application this gives the following
> exception (translated from Swedish).
>
> "The parameter object is badly defined. The supplied information is
> inconsistent or incomplete"
>
> If I only use dates instead of date + time then everything works ok.
>
>
> Does anyone know why this happens?

I suspect ODBC helps iterpret #12/31/2007 23:59:00# correctly. (Is it not a
barbaric construction?).

With ADO I would use
CAST('2007-12-31 23:59:00' As DateTime) or SmallDateTime depending on the
size of the field.

Upon further review I would use
WHERE YEAR(ts) = 2007

--
lyle fairfield