Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Red face Unanswered: How to check a timestamp field with ODBC connection

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).

  4. #4
    Join Date
    Oct 2003
    Posts
    5

    Red face

    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.
    Last edited by itfabbste0; 10-16-03 at 08:24.

  5. #5
    Join Date
    Oct 2003
    Posts
    5

    Talking

    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.

  6. #6
    Join Date
    Oct 2003
    Posts
    5

    Angry

    Marcus

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

    Thanks, S.

    Originally posted by Marcus_A
    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).

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  8. #8
    Join Date
    Oct 2003
    Posts
    5

    Talking

    Grazie 1000

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •