Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unanswered: Select between given dates based on Current Month

    Hi friends,
    I am looking for a help on building a DB2 SQL query.

    I have a requirement to Unload rows from a table based on the Current Month I am runnig the program. This is a Unload job and need to coded in JCL/sysins ( not a Cobol or any other) I need to pull all the records starting 8th of previous month to 8 th of this month. ( entry_date between Date 8th of previous month to Date 8th of Current Month).

    eg: if I need to run this process anyday in the Month of January
    select * from job_table where entrydate between (2010-12-08 and 2011-01-08)

    And like wise the quey would work as below when I run in February
    select * from job_table where entrydate between (2011-01-08 and 2011-02-08)

    Could anyone please help me in building this dynamic query to be embeded in a SYSIN.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    Code:
     WHERE entrydate
           BETWEEN CURRENT_DATE - ( DAY(CURRENT_DATE) - 8 ) DAYs - 1 MONTH
               AND CURRENT_DATE - ( DAY(CURRENT_DATE) - 8 ) DAYs
    Anyhow, I'm interested in the following fact.
    Is it really your requirement?
    If "to pull all the records starting 8th of previous month to 8 th of this month",
    then "the row of 8th of a month" would be included in both of unload job run at previous month and this month.

  3. #3
    Join Date
    Jan 2011
    Posts
    2
    This works great.. I am able to get the expected results.

    Yes, my requirement is to get all the records Starting from 9th previous month till 8th of current month. I just incremented the first date to be 1 so that, I will not have 8th on both ends.

    WHERE entrydate
    BETWEEN CURRENT_DATE - ( DAY(CURRENT_DATE) - 9 ) DAYs - 1 MONTH
    AND CURRENT_DATE - ( DAY(CURRENT_DATE) - 8 ) DAYs


    THanks a lot mate..!!

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
  •