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

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)

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

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...

Petruk

it's excelent!!
it's working

Thanks

By the by i forgot ask one thing
I want show this result for the given 2 timestamps

how do we do that?

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

Add the WHERE clause back in before the GROUP BY.

And pick up a book on SQL.