Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Unanswered: DB2 SQl statement

    Hi all,

    I have a table with a column Frequency which is deimal of two digits, indicates the number of months in decimal (36 Months). i have to build a query in such a way that i have to deduct from the system date.

    Example:

    Select X,Y,Z from Table where End date < (System date - Frequency)

    i have to convert 36 months to a date from the system date.

    can you help me out in this regard.

    Rgds
    Raghu

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    select x,y,z from table where end_date < (current date - frequency months)

    Andy

  3. #3
    Join Date
    Jan 2008
    Posts
    3
    Hi andy,

    i want to convert the frequency months to a date(dd/mm/yyyy) format subtracting from the Current date.

    Awaiting for your reply.

    Rgds
    Raghu

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is what I gave you, or I do not understand what you want.

    Andy

  5. #5
    Join Date
    Jan 2008
    Posts
    3

    Smile DB2 SQL Statement

    Hi andy,

    I tried your sql statement it is not working....

    Query that i have tried:

    SELECT VW450.LAR_ID,
    VW450.ACNT_ID,
    VW450.BEG_DT,
    VW450.EIND_DT,
    VW455.PURGE_FREQ
    FROM FPOIS912.VW450_LAR_OVK AS VW450,
    FPOIS912.VW455_LAR_EXP_FREQ AS VW455
    WHERE VW450.LAR_ID BETWEEN 'LAR1000001' AND 'LAR1000031'
    AND VW450.EIND_DT < (CURRENT DATE - VW455.PURGE_FREQ)

    could you help me out in this regard.

    Rgds
    Raghu

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by RaghuING
    AND VW450.EIND_DT < (CURRENT DATE - VW455.PURGE_FREQ)

    Raghu

    AND VW450.EIND_DT < (CURRENT DATE - VW455.PURGE_FREQ MONTH )


    you omitted the MONTH keyword ...

  7. #7
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    Just a cautionary note about using date durations:

    Code:
    When adding durations to dates, adding one month to a given date
    gives the same date one month later unless that date does not exist in the
    later month. In that case, the date is set to that of the last day of the later
    month. For example, January 28 plus one month gives February 28; and one
    month added to January 29, 30, or 31 results in either February 28 or, for a
    leap year, February 29.
    
    Note:
    If one or more months is added to a given date and then the same number of
    months is subtracted from the result, the final date is not necessarily the 
    same as the original date.

Posting Permissions

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