I am using DB2 first time. I have a table with Date, Time fields.
Field names are Date , Time

For each date we have many Claims with different timestamps.

But I need to get only the Claims with most recent timestamp into my query results

How Can I achieve this

Both Date and Time fields are Date data type

I tried using Max(Time) which is giving me some number 1/0/1900

What DB2 version and OS?

DB2 version 8. But I am not sure about OS

I would suggest that you find out what the OS (Operating System) is. It makes a lot of difference in the answers you will get. Some things are valid for Z/OS but not for Linux, or Windows, or Unix.

Andy

That is not possible. You cannot store a time in a DATE data type.

I think there are just badly chosen column names.

It is possible. I used Timestamp function to make it as a DateTime field and then did max(Datetime) field which is working like a charm

Markus had a completely different point: a DATE value is not a timestamp and a TIMESTAMP value is not a date. Instead, a TIMESTAMP value is a combination of a DATE value and a TIME value. And you cannot store a TIME value in a value of type DATE. There is no arguing about it.

If you used the TIMESTAMP function on a DATE, you have a TIMESTAMP value as result - not a DATE value.

I agree with it. But on the whole I need to see max(timestamp) for each date. When I did max(Timestamp) on Time field which gave me wrong results (I mentioned in my thread). If I do max(Timestamp(Date, Time)), I am getting it

Sorry, it's hard to understand what you try to say because you seem to mix data types with column names - e.g. "max(Timestamp) on Time field" doesn't make any sense. Is the column named "Timestamp" and has a data type "Time"? That's extremely confusing and you should fix this.

p.s: There are no "fields" in relational database systems. You have only "tables" with "rows" and "columns", and each row has a "value" for each column.