Forum Stats

  • 3,726,957 Users
  • 2,245,297 Discussions
  • 7,852,509 Comments

Discussions

How do I get the month or year from a date picker.

User_QU88A
User_QU88A Member Posts: 6 Green Ribbon

I am just getting started in APEX. I have a table with DATE, MONTH, and YEAR columns. My user will select the DATE using a date picker component. When the date is chosen I would like to populate the MONTH and YEAR components automatically.

What would that code look like and where exactly do I place it so it updates MONTH and year every time it changes before the form is submitted.

Any help would be appreciated.

Tagged:

Best Answer

  • User_QU88A
    User_QU88A Member Posts: 6 Green Ribbon
    Accepted Answer

    This is the solution I ended up using:


    Dynamic Action = Execute Server-side code

    This code:

    :P4_MONTH := TO_CHAR(TO_DATE(:P4_MY_DATE, 'MM/DD/YYYY'),'MONTH');

    :P4_YEAR := TO_CHAR(TO_DATE(:P4_MY_DATE, 'MM/DD/YYYY'),'YYYY');

    Items to Submit = P4_MY_DATE

    Items to Return = P4_MONTH,P4_YEAR

Answers

  • Mr.Peabody
    Mr.Peabody Member Posts: 10 Red Ribbon

    Use the following code to select month and similary year ('YYYY' or 'YY') also in your insert statement.

    TO_CHAR(:PAGE_ITEM_NAME, 'MM')
    

    I hope it will help 😉

  • User_QU88A
    User_QU88A Member Posts: 6 Green Ribbon

    That is a start, but how do I tie the page item value of the date picker to the page item value of the Month column?

    I am trying to use a dynamic action on the date picker item to use that code but I can't get it setup correctly.

  • jariola
    jariola Member Posts: 10,191 Bronze Crown
    edited April 10

    What is data type for your DATE, YEAR and MONTH columns?

    I propose that you have only one column that data type is DATE. When you query data, you can get e.g. only year and month using to_char or in APEX reports using format mask.

    Depending your database version, you can also have virtual column that shows e.g. year and/or month from DATE. Or create view.

    Here is small example for virtual column and view

    create table dt(
     id number primary key,
     entry_date date not null,
     entry_year varchar2(4) as ( to_char( entry_date, 'fmYYYY' ) ) virtual 
    );
    
    insert into dt( id, entry_date ) values( 1, sysdate);
    commit;
    
    select *
    from dt
    ;
    
    create view v_dt as
    select id
      ,entry_date
      ,entry_year
      ,to_char( entry_date, 'fmMM') as entry_month
      ,to_char( entry_date, 'fmDD') as entry_day
    from dt
    ;
    
    select *
    from v_dt
    ;
    

    Or do you have some very special reason have separate columns for year, month and date?

    fac586
  • User_QU88A
    User_QU88A Member Posts: 6 Green Ribbon

    DATE, NUMBER, VARCHAR2 for Date, Year, and Month.

    I added the columns to make the faceted search easy to setup and the interactive report easy to filter. Maybe not elegant but it works.

    This is also a learning exercise for me. I want to know how to set a value for a page item based on another page item. I am struggling with what kind of event to choose and how to setup the entire flow.

  • jariola
    jariola Member Posts: 10,191 Bronze Crown
    edited April 10

    In APEX you can use after submit computation to set item value from another item before processes. In computation you can transform value for YEAR and MONTH columns from date picker item

    https://docs.oracle.com/en/database/oracle/application-express/20.2/htmdb/understanding-page-computations.html#GUID-9EF283E4-0FE1-4CA5-A5BA-0A8A8CC9BA31

    But, I think it unnecessary have separated columns, as you can create view or add virtual columns to table. If you have virtual columns, you can also index those for performance.

  • User_QU88A
    User_QU88A Member Posts: 6 Green Ribbon

    I am not looking for performance of anything like that, I am just trying to learn how to set up a simple dynamic action to extract a month from date and fill that into a column (a year as well). What I have done so far is:

    Added a dynamic action to the date picker component

    Event is Change, Selection Type is Item, Item(s) is my date picker component

    Client-side condition is Item != null

    Action is Set Value

    Set Type is PL/SQL expression (-------> if I use a static value setup it works fine)

    What I seem to be missing is a valid PL/SQL expression to get the month/year from the date field and assign it to the MONTH column..

    My Item to Submit the is date picker item.

    I have tried this and get an error: "Ajax call returned server error ORA-06502: PL/SQL: numeric or value error: character to number conversion error for Set Value."

    TO_CHAR(:P5_DATE_REC, 'MM')

    Any thoughts?

  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,142 Silver Trophy

    Why using many columns for date?! A date column and it's corresponding item are sufficient.

  • User_QU88A
    User_QU88A Member Posts: 6 Green Ribbon
    edited April 11

    I already explained several times that I am using the extra columns just as a simple test case - I don't know why everyone is so focused on that. 🙄

    I am looking for help on getting the month from the date, and then applying it to another page item via a dynamic action.

  • Eslam_Elbyaly
    Eslam_Elbyaly Member Posts: 4,142 Silver Trophy
    edited April 11

    in an onChange dynamic action use,

    $s("P12_NEW",$v("P12_DATE").substr(2,1));

    Change the start and end position according to your date item format mask. P12_DATE is the one with date picker.

  • User_QU88A
    User_QU88A Member Posts: 6 Green Ribbon
    Accepted Answer

    This is the solution I ended up using:


    Dynamic Action = Execute Server-side code

    This code:

    :P4_MONTH := TO_CHAR(TO_DATE(:P4_MY_DATE, 'MM/DD/YYYY'),'MONTH');

    :P4_YEAR := TO_CHAR(TO_DATE(:P4_MY_DATE, 'MM/DD/YYYY'),'YYYY');

    Items to Submit = P4_MY_DATE

    Items to Return = P4_MONTH,P4_YEAR

Sign In or Register to comment.