SELECT problem using ADO

[k.renaud]

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?

[lyle fairfield]

k.******@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