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

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.

Thanks for the reply flyboy,
I'll try that..