Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    7

    Unanswered: How to get Max timestamp of a Date

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS?

  3. #3
    Join Date
    Aug 2008
    Posts
    7
    Quote Originally Posted by ARWinner
    What DB2 version and OS?
    DB2 version 8. But I am not sure about OS

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by sushmitha
    Both Date and Time fields are Date data type
    That is not possible. You cannot store a time in a DATE data type.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    That is not possible. You cannot store a time in a DATE data type.
    I think there are just badly chosen column names.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2008
    Posts
    7
    Quote Originally Posted by Marcus_A
    That is not possible. You cannot store a time in a DATE data type.
    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

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by sushmitha
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Aug 2008
    Posts
    7
    Quote Originally Posted by stolze
    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

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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