March 20, 2013 at 10:11 am
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
"Nicholas"
March 22, 2013 at 11:27 am
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
This might be a bit over engineered, would you please mind to share the definition of MONTH_YEAR column as well as an example of how data looks like there?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply