Results 1 to 3 of 3
  1. #1
    Join Date
    May 2007
    Posts
    1

    Unanswered: How to compare TIMESTAMP with DATE

    I need to convert a timestamp to a date, then extract only the rows that match current date.

    Here is what I have:
    select endtime, substr(endtime,4,2) || '/' || substr(endtime,6,2) || '/20' || substr(endtime,2,2) MOD_ENDTIME from warehouselog fetch first 2 rows only

    ENDTIME MOD_ENDTIME
    ---------------- -----------
    1061108223000000 11/08/2006

    The latest query I have attempted and the results:
    select substr(endtime,4,2) || '/' || substr(endtime,6,2) || '/20' || substr(endtime,2,2) ENDTIME from warehouselog where date(ENDTIME) >= DATE(CURRENT TIMESTAMP)
    Do you want to execute the above command ? (y/n) y

    ENDTIME
    ----------
    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007

    Any help on this one is greatly appreciated.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    First of all, ENDTIME is not a timestamp, it is a character string. You will have to convert it to something that DB2 can recognize as a date-string. you need something like:

    select substr(endtime,4,2) || '/' || substr(endtime,6,2) || '/20' || substr(endtime,2,2) ENDTIME from warehouselog where date(substr(endtime,4,2) || '/' || substr(endtime,6,2) || '/20' || substr(endtime,2,2)) >= CURRENT DATE

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Andy already gave you the answer. I would like to add that you could implement a simple function that does the conversion:
    Code:
    CREATE FUNCTION strToDate(str VARCHAR(16))
       RETURNS DATE
       LANGUAGE SQL
       DETERMINISTIC
       CONTAINS SQL
       RETURN DATE(substr(str, 4, 2) || '/' || substr(str, 6, 2) || '/20' || substr(str, 2, 2));
    No you can simplify your query to:
    Code:
    SELECT strToDate(endTime) AS endtime
    FROM   warehouselog
    WHERE  strToDate(endTime) >= CURRENT TIME
    Note: you should really reconsider your column alias ENDTIME. You return a date and not a time value. Thus, the chosen alias is wrong and confusing.

    p.s: Have a look at the DB2 manual (or the SQL standard if you prefer) to understand the differences between TIMESTAMP, DATE, and TIME values. Converting a timestamp to a date does not make much sense. Extracting the date portion from a timestamp does.
    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
  •