I have a table on DB2 (AS400) with a Timestamp Column. I'd like the select all the field from that table where the timestamp column is greter than a specific time.

From AS400 console a can use this syntax without error:
SELECT * FROM srossi/andip00f WHERE TMSVAR <= '2003-07-20-18.00.00.000000'

But unfortunately on Access it is not the same. Do you know how to solve the problem?

Many Thanks, Ciao, Stefano

I don't know the answer, but I would be interested in knowing how Access displays a timestamp selected from DB2. That might help you figure out how Access handles that data type.

One more thing. Try the same SQL statement from a command prompt on your Windows machine. That way you can tell if it is the ODBC driver or just Access. I assume you are using the IBM ODBC driver that gets installed with the Runtime Client (or any other DB2 client).

I have already verified how Access displays timestamp

16/10/2003 12:51:23
( As400 shows '2003-07-20-18.00.00.000000'

I have tried with:

1) SELECT SROSSI_ANDIP00F.* FROM SROSSI_ANDIP00F WHERE SROSSI_ANDIP00F.TMSVAR<="01/01/2003 17:00:00";
2) ......TMSVAR<="01-01-2003-17:00:00.000000";
3) ......TMSVAR<="2003-01-01-17.00.00.000000";(like As400 syntax)
4) ......TMSVAR<="01-01-2003 17:00:00";

but unfortunately access shows me "Data Tyme mismatch in criteria expression" each time.

Thanks a lot, Ciao, S.

N.B. I'm using the ODBC driver of DB2 Connection versione 8.1 of IBM, in order to create a ODBC connection with the AS400. Now I'm trying to select via MS Access with that ODBC.

I found one Access function that solve my problem, it trasforms a string into a date. The function is DATEVALUE

Unfortunately it seems that Access does not consider the expression about the Hour, infact the two statemets here attached, extract the same data

SELECT SROSSI_ANDIP00F.* FROM SROSSI_ANDIP00F WHERE (((SROSSI_ANDIP00F.TMSVAR) >= DateValue("2003-06-05 13.37.54")));

SELECT SROSSI_ANDIP00F.* FROM SROSSI_ANDIP00F WHERE (((SROSSI_ANDIP00F.TMSVAR) >= DateValue("2003-06-05 13.37.53")));

Ciao, S.

Marcus

How can I execute the same statement from the command prompt of windows?

Thanks, S.

The easiest way is to install the runtime client (or any client) and run the client configuration assistant to catalog the remote database on the local machine. Then you can issue db2 commands like:

db2 connect to database-name ......
db2 SELECT * FROM ...

You might have already installed a client, so try running the db2 commands.

However I am not really certain what is needed when talking to DB2/400 from Windows client.

Grazie 1000