Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009

    Unanswered: Simple Date Query Not So Simple

    I'm fairly new to SQL and to this point my experience is mostly limited to Access. However, I'm trying to create a pass through query to a DB2 table and I'm having all kinds of headaches trying to set it up.

    The query is very simple:

    SELECT *
    WHERE REQT_TMSP > '2006-01-01'
    The REQT_TMSP field is a time stamp of when that request record was added to the table and when I view the table the format is:

    MM/DD/YYYY 00:00:00 AM/PM

    I've tried a ton of stuff to get this query to work but I keep getting syntax related errors. There is probably something very basic that I am missing but I can't seem to figure it out and I'm not finding much in Google that's helping.

    Any ideas?

  2. #2
    Join Date
    May 2009
    Provided Answers: 1
    icurschwarz, the timestamp format in DB2 is:

    (The last 6 digits are the micro seconds).

    There are a few ways to fix your query.

    WHERE REQT_TMSP > TIMESTAMP('2006-01-01', '24.00.00')
    WHERE REQT_TMSP > '2006-01-01-'
    (I don't recommend the next one if REQT_TMSP is indexed or could be indexed.)
    WHERE DATE(REQT_TMSP) > '2006-01-01'

    Where are you viewing the result that shows the AM/PM in the Timestamp?

  3. #3
    Join Date
    Jan 2007
    Jena, Germany
    Don't mix up TIMESTAMP, DATE, and TIME values. They serve different purposes and cannot directly be compared (for good reasons).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2009
    Thanks guys. That was what I needed.

Posting Permissions

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