Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    3

    Unanswered: How to set date minus days in SQL

    I was told to substitute a SELECT stmt with SET.
    Select DATE (:WS-DATE) - 1 days
    How do I do that? Is it pissible? I tried different syntax but still getting errors with SET. Probably DBA did not know that it is not doable.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Read this useful page
    http://www.ibm.com/developerworks/da.../0211yip3.html

    It's useless to say "getting errors" without specifying the exact error.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Select DATE (:WS-DATE) - 1 days
    If this was a whole SQL statement,
    it violates syntax of DB2.
    Because, a "SELECT ..." in DB2 needs "FROM clause".

    Some examples of valid syntax on DB2 are...
    Code:
    VALUES DATE(:WS-DATE) - 1 days;
    /* or */
    SELECT DATE(:WS-DATE) - 1 days FROM sysibm.sysdummy1;
    /* or */
    VALUES DATE(:WS-DATE) - 1 day;
    /* or */
    SELECT DATE(:WS-DATE) - 1 day FROM sysibm.sysdummy1;
    /* so on ... */

    By the way,

    the link provided by db2mor may be a must to understand for all persons who want to work with DB2.

  4. #4
    Join Date
    Oct 2014
    Posts
    3

    Red face let me clarify

    Old SQL statement:
    SELECT DATE(:WS-REFORMAT-DB2) - 1 DAYS
    INTO :WS-DATE-AREAS.WS-EOM-DB2
    FROM T00abc11

    Requiremnt for a new statement:
    Change SELECT above to SET statement so DB2 does not need to do any I/O and reference a table unnecessarily.

    I changed to SET but was getting SQLCODE=-182,

    SET :WS-EOM-DB2 = :WS-EOM-DB2 - 1 DAYS

    Is it possible to use SET and subtract days? Thank you very much to everyone who responds.
    Marina
    Last edited by marina908; 10-28-14 at 15:53.

  5. #5
    Join Date
    Oct 2014
    Posts
    3

    Thumbs up found correct syntax

    SET :WS-REFORMAT-DB2 = DATE(:WS-REFORMAT-DB2) - 1 DAYS

    where ws-reformat-db2 is pic x(10) in mm/dd/ccyy format

Tags for this Thread

Posting Permissions

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