Order dates in DESC order, starting with current Month/Year

  • 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"

  • 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