Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Unanswered: subtracting 5 days from a date fetched as a result of a db2 select query

    Hi,
    I want to subtract say 5 days from the date obtained as the result of a db2 sql query.

    I was trying this
    Code:
    SELECT  ((SELECT ENROLL_DATE FROM RECORDS.ENROLL_RECORDS WHERE FEE_RECEIPT_NO=5002) -5 DAYS ) FROM SYSIBM.SYSDUMMY1
    but it doesn't work.

    WHILE this works:
    Code:
    SELECT  (DATE('1997-04-03')-5 DAYS) from sysibm.sysdummy1
    When I just execute
    Code:
    SELECT ENROLL_DATE FROM RECORDS.ENROLL_RECORDS WHERE FEE_RECEIPT_NO=5002
    it gives me 02/15/2010

    Plz help.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Does the last query happen to return more than 1 row? If so, you would try to subtract 5 days from a set of rows. That doesn't work, of course, because a set of rows is not a DATE value.

    Try this instead:
    Code:
    SELECT ENROLL_DATE - 5 DAYS
    FROM    RECORDS.ENROLL_RECORDS
    WHERE   FEE_RECEIPT_NO=5002
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Mar 2011
    Posts
    4
    Thanks for the prompt reply.

    No, that query only returns 1 single value (1 row, 1 column).
    And I forgot to say that ENROLL_DATE is not a db2 date but a varchar type.
    So I can't try this

    Code:
    SELECT ENROLL_DATE - 5 DAYS
    FROM    RECORDS.ENROLL_RECORDS
    WHERE   FEE_RECEIPT_NO=5002
    But I am sure that the string that
    Code:
    SELECT ENROLL_DATE - 5 DAYS
    FROM    RECORDS.ENROLL_RECORDS
    WHERE   FEE_RECEIPT_NO=5002
    returns is in db2 date format. It's just not stored as a db2 date in the table its retrieved from.

    Now can u guide me plz. ?

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    First, if you have a Date value, it should be stored in a DATE data type column. Otherwise you are wasting space (4 bytes compared to 8 or 10 bytes (depending on if you store the separator)).

    Second, if you have to store it as text, it should be CHAR and not VARCHAR as it will use less space. Since all dates would be 8 or 10 characters, the VARCHAR doesn't get you anything and wastes 2 bytes.

    If you store the value in a valid date format, this should get you what you want:
    Code:
    SELECT DATE(ENROLL_DATE) - 5 DAYS
    FROM    RECORDS.ENROLL_RECORDS
    WHERE   FEE_RECEIPT_NO=5002
    You could also use CAST:
    Code:
    SELECT CAST(ENROLL_DATE AS DATE) - 5 DAYS
    FROM    RECORDS.ENROLL_RECORDS
    WHERE   FEE_RECEIPT_NO=5002

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by DushyantG View Post
    And I forgot to say that ENROLL_DATE is not a db2 date but a varchar type.
    So you have a string and not a date. The string value is something you want to have interpreted as date. SQL (in general) has strong typing. Thus, you have to apply some type conversion before.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

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
  •