Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    54

    Unanswered: SQL0401N The data types of the operands for the operation "BETWEEN" are not compatib

    Hi,

    I used the following command for DB2 v7

    SELECT count(MSGTYPE) from db2admin.MQMD where TSTAMP BETWEEN date('2004-01-01') and date('2004-01-10')

    it says
    SQL0401N The data types of the operands for the operation "BETWEEN" are not compatible. SQLSTATE=42818

    I appreciate ur help!

    Thanks

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Is TSTAMP a TIMESTAMP?

    You can't use a DATE range for a TIMESTAMP column.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by J Petruk
    Is TSTAMP a TIMESTAMP?

    You can't use a DATE range for a TIMESTAMP column.
    I should give a suggestion too...

    BETWEEN '2004-01-01-00.00.00.000000' AND '2004-01-11-00.00.00.000000'

    (Note 01-11 instead of 01-10... inclusive)
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Mar 2004
    Posts
    54
    as Jonathan Petruk said it's working fine.

    But it's returning 0 count though it has records in the table between those 2 days.

    Let me explain
    I have a table called MQMD
    I have TSTAMP, MSGID, MSGTYPE as columns.
    i have 3 unique MSGTYPEs
    they are 1, 8, 9

    I need result like this

    MessageType count
    --------------- -------
    1 100
    8 65
    9 17

    Now please correct my query.

    Thanks

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    You need to use GROUP BY.

    SELECT MSGTYPE, COUNT(*)
    FROM MQMD
    GROUP BY MSGTYPE;

    I charge by the hour, the invoice is in the mail...
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Mar 2004
    Posts
    54

    Thumbs up

    Petruk

    it's excelent!!
    it's working

    Thanks

  7. #7
    Join Date
    Mar 2004
    Posts
    54
    By the by i forgot ask one thing
    I want show this result for the given 2 timestamps

    how do we do that?

  8. #8
    Join Date
    Mar 2004
    Posts
    54
    o.k. i could figured it out

    db2 => SELECT MSGTYPE, COUNT(*) count FROM db2admin.MQMD WHERE TSTAMP BETWEEN '2004-03-28-00.00.00' AND '2004-03-29-00.00.00' GROUP BY MSGTYPE

    MSGTYPE COUNT
    ----------- -----------
    1 1505
    2 1684

    2 record(s) selected.

    Thanks

  9. #9
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by manth
    By the by i forgot ask one thing
    I want show this result for the given 2 timestamps

    how do we do that?
    Add the WHERE clause back in before the GROUP BY.

    And pick up a book on SQL.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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