locked
Retirieving based on Month and year form date-oracle RRS feed

  • Question

  • User-1578974752 posted

    Hi

    Actually

    select distinct

    user_name

    from usermaster where

    SUPPLIER_CODE=seg1

    and CHECK_DATE between '01-JAN-2021' and '31-JAN-2021'

    above query is working fine , but I want as below

    If select a month and year of CHECK_DATE in the drop downlist the value must show in the grid view.

    when I tried as below in oracle, it is not working.

    to_char(CHECK_DATE, 'MONTH') ='JAN'

    to_char(CHECK_DATE, 'YEAR') ='2021'

    ie

    select distinct

    user_name

    from usermaster where

    SUPPLIER_CODE=seg1

    and to_char(CHECK_DATE, 'MONTH') ='JAN'

    and to_char(CHECK_DATE, 'YEAR') ='2021'

    what change should I do ,to make it work. Thanks

    Friday, January 22, 2021 5:29 AM

Answers

  • User1535942433 posted

    Hi shsu,

    As far as I think, the Oracle TO_CHAR()'s syntax is wrong.

    Syntax:

    TO_CHAR(expr [, date_format] [, nslparam]);

    1) expr

    The expr is a DATE or an INTERVAL value that should be converted. It's CHECK_DATE in your query.

    2) date_format

    The date_format is a string that determines the format that the result string should be in.

    'JAN' format is MON.  Format 'MONTH' is 'JANUARY'.

    '2021' format is YYYY.

    More details,you could refer to below article:

    https://www.oracletutorial.com/oracle-basics/oracle-date-format/

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 25, 2021 7:22 AM