Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Unanswered: Querying data for certain month-year interval

    Hi all,
    I try to query data that falls into certain month-year range.
    In the report, I have 2 string parameters, namely startMonth and startYear.
    For the startMonth and startYear selected by user, we will query the data starting from that month-year pair to the next 3 months.
    So, let's say user selects November for startMonth and 2011 for startYear, the fetched data will be that from November 2011 to January 2012.
    I have a sales table as below:
    Table Sales
    ID integer,
    TRANSACTION_DATE date,
    CHANNEL char(3),
    AMOUNT number

    And I also made the query below:

    SELECT TO_CHAR(TRANSACTION_DATE, 'MM/YYYY') AS SALES_PERIOD, CHANNEL, SUM(AMOUNT) AS AMOUNT
    FROM S_SALES
    WHERE TO_DATE(TO_CHAR(TRANSACTION_DATE, 'MM/YYYY'), 'MM/YYYY') BETWEEN TO_DATE(${startMonth} || '/' || ${startYear}, 'MM/YYYY')
    AND ADD_MONTHS(TO_DATE(${startMonth} || '/' || ${startYear}, 'MM/YYYY'), 3))
    GROUP BY TO_CHAR(TRANSACTION_DATE, 'MM/YYYY'), CHANNEL

    But the fetched result is not like what I expect.
    I tried to analyze the query and it seemed that there is something wrong. Part of the query above (TO_DATE(TO_CHAR(TRANSACTION_DATE, 'MM/YYYY'), 'MM/YYYY') ) will always return month/year with it's date part. What I get is 12/01/2011 instead of 12/2011.

    So, How can I select data that falls into certain month-year range ?
    Thanks in advance

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    firstly, there is no need for that TO_DATE(TO_CHAR()) conversion - it just truncates the date to the start of the month. It may be achieved by using TRUNC( transaction_date, 'MM' ). Obviously, this expression still has DATE data type, so it contains year/month/day/hour/minute/second - it is just filled with the start of that period (midnight in the 1st day of that month in your example).

    Anyway, you should not make any manipulation on TRANSACTION_DATE and use correct dates in ranges instead. E.g.
    Code:
    where transaction_date >= to_date( <obtained from params as in your query> )
      and transaction_date < add_months( to_date( <obtained from params> ), 3 )

  3. #3
    Join Date
    Jul 2011
    Posts
    4
    Thanks for the reply flyboy,
    I'll try that..

Posting Permissions

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