Hi
i'm trying to run this:

select *
from openquery(DB2_SERVER,
'SELECT *
FROM DB2USER.DB2TABLE
where (TableDateTime between CURRENT_DATE-1 DAYS AND CURRENT_DATE )')
i get an error :
The data types of the operands for the operation "BETWEEN" are not compatible

no operation works i know that CURRENT_TIMESTAMP is working .

HELP !!!!!!!!

Please look at the query.
What is the word DAYS doing there?

tryed :between CURRENT_DATE-1 AND CURRENT_DATE
not working.
the data type of : TableDateTime is smalldatetime.

What does it mean "not working"? Did you get any error, if so how about posting it here? Why dont you try using DATEDIFF() for date calculations rather than doing a straight "- 1" there ?

_________________________
- Vijay G
Strive for Perfection

Ok
i'm talking about DB2 working with sqlserver2000 :
the problem:i'm trying to compair-
smalldatetime with CURRENT_DATE on DB2 working with sqlserver.
TableDateTime is smalldatetime like : 29/04/2007 15:12:21.0000 .

what i'm trying to do is to take yesterday so i ran this first :

select *
from openquery(DB2_SERVER,
'SELECT *
FROM DB2USER.DB2TABLE
where (TableDateTime CURRENT_TIMESTAMP -1 DAYS-5 HOURS AND CURRENT_TIMESTAMP )')

and its working well but i have more/less then one day and i need only one day yesterday .
DATEDIFF() ? no function on DB2(openquery) .
i can solve it with CURRENT_TIMESTAMP then convert to date and delete the none yestrday ones .

select *
from openquery(DB2_SERVER,
'SELECT *
FROM DB2USER.DB2TABLE
where (TableDateTime between CURRENT_DATE-1 DAYS AND CURRENT_DATE )')
error :
The data types of the operands for the operation "BETWEEN" are not compatible.


select *
from openquery(DB2_SERVER,
'SELECT *
FROM DB2USER.DB2TABLE
where (TableDateTime between CURRENT_DATE-1 AND CURRENT_DATE )')

error :
An expression with a datetime value or a labeled duration is not valid .

I dont have DB2 environment to see how that works. Moreover I am not sure what it returns when CURRENT_DATE-1 is executed there. From the Error that you posted, all I can think of is that the CURRENT_DATE-1 OR CURRENT_DATE seems to be resulting different types of values which is not conforming with that of the the TableDateTime. Try and check if that is a possiblity for this error.
It is better to do the following from your OPENQUERY() and see if all the 3 return values of similar datatype.
SELECT CURRENT_TIMESTAMP -1 DAYS-0 HOURS, CURRENT_TIMESTAMP -1 DAYS, CURRENT_TIMESTAMP

I see "BETWEEN" missing in the below code...
(TableDateTime CURRENT_TIMESTAMP -1 DAYS-5 HOURS AND CURRENT_TIMESTAMP )
But the same being used in the other one. Is that you missed it by mistake? When you say this one works...then see if this helps...
CURRENT_TIMESTAMP -1 DAYS-0 HOURS

Cheers.

_________________________
- Vijay G
Strive for Perfection

Thank's
1.CURRENT_TIMESTAMP -1 DAYS-0 HOURS ==CURRENT_TIMESTAMP -1 DAYS.
it's date-1 + now time .0 Hours .
2.my mistake .
3.on sqlserver the data type is the same datetime.
also CURRENT_DATE and TableDateTime are datetime.it's DB2 that returns the error.