5280_Lifestyle (3/20/2013)

---

I'm trying to get month and year output for an SSRS report using an Oracle data source for use as a multiple value parameter; the user can select dates such as February 2013, January 2013, October 2012. My current query does that, but it does not start at February 2013, it starts at December 2013. The MONTH_YEAR field is populated with month/year up to December 2050, which is why I'm trying to limit the parameter's starting date to choose from the current month and year.

(I was going to post this in Reporting Services, but since it's the Oracle code that I want altered this seemed a more appropriate location.)

SELECT DISTINCT MONTH_YEAR

FROM EDL.V_CLNDR

WHERE SUBSTR(MONTH_YEAR,1,INSTR(MONTH_YEAR,' ')-1) <= TO_CHAR(SYSDATE,'Month')

AND SUBSTR(MONTH_YEAR, -(LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' ')), (LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' '))) <= TO_CHAR(SYSDATE,'YYYY')

ORDER BY

SUBSTR(MONTH_YEAR, -(LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' ')), (LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' '))) DESC,

TO_DATE(UPPER(SUBSTR(MONTH_YEAR,1,INSTR(MONTH_YEAR,' ')-1)),'MONTH','nls_date_language=american') DESC