12 Replies Latest reply on Nov 5, 2013 5:36 PM by TexasApexDeveloper

    Date Picker Translate Help




      We've been using jQuery date pickers long before APEX used them as standard so have our own way of implementing them, that being said it shouldn't have any effect on what I'm trying to do.


      I have an application (APEX 4.2.1) that is used in a number of different languages. We don't use the APEX date picker and instead display dates as text fields, add a specific class to them and attributes as required. The class is picked up in javascript and the field is converted to a jQuery UI date picker with the attributes controlling certain behaviour (date restrictions etc.) This works fine and the date picker is translated to the different languages without a problem.


      Now I have a page in which fields defined by the user are displayed along side APEX fields. To do this I'm using apex_item and generating items on the page based on settings controlled by the user in another part of the application. The APEX items are displayed followed by custom items using apex_item. Again, this works fine and I've used it many times before.


      The problem I have is when a user creates a custom field which is a date picker and the date picker is translated.


      If the user is viewing the page in Norwegian then for an APEX field the date picker is shown in Norwegian and if they select May 1st then 1-Mai-2013 is returned to the field. The value of this field is 1-Mai-2013 when the page is submitted, but somehow the value is saved in the database correctly and when the database is queried the date is saved as 01-MAY-2013. For a custom field the value is saved in the database as 1-Mai-2013 and when this is then viewed by a user in English the page errors with not a valid month.


      My questions are:

      - How does APEX save a translated date field? How does 1-Mai-2013 become a proper date? If I try to save this in the database, unsurprisingly I get an invalid month error.

      - How can I use apex_item to have a date picker that is translated? apex_item.date_popup can't be used as this doesn't use the jQuery date picker. Perhaps this is a setting in jQuery UI to translate the date back? One solution is to use dd/mm/yyyy format, but I really don't want to do this.


      Many thanks for your help. If you need any more info let me know.



        • 1. Re: Date Picker Translate Help
          Mike Kutz

          SaraB wrote:

          the database is queried the date is saved as 01-MAY-2013..

          No it isn't.  The database stores DATES in a custom format that totally ignores such problematic things such as the spelling of "MAY"/"MAI" in different countries.

          This is only one, of many, reasons why you must always store DATE data in a DATE column.


          In SQL*Plus/SQL Developer, run this:


          Now, when you select the date, it will show up as '01-MAI-2013'.




          Now, when you select the date, it will show up as '01-MAY-2013'.


          It sounds like your code is assuming the database session is using one NLS_LANGUAGE setting when it should be using the other.


          select to_date('1-MAI-2013','DD-MON-YYYY') from dual;

          by default, I get an ORA-01843 because my DB's NLS_LANGUAGE is set to 'AMERICA'.

          However, if I alter the session to be 'NORWEGIAN', it works.


          I can only assume something like this needs to happen within your APEX system.

          .. and your Date Picker will need to return May or Mai based on that setting.


          Some others may be able to help point you to the correct method for ALTER SESSION within APEX based on user.

          (although, google could help also.)

          • 2. Re: Date Picker Translate Help

            Hi Mike


            Thanks for the reply. When I said that it's saved as 01-MAY-2013, I was aware that it wasn't actually saved as this and is stored as a date. My point was that as a user I had entered the text 1-Mai-2013 on the screen and somehow it was changed to a date which when queried in the database was returned correctly as 01-MAY-2013. Showing that somehow it had been translated correctly from Norwegian. Even if I were to save my custom field value in a date column in the database, I would get an error as it would be trying to insert 1-Mai-2013 into a date column which isn't of the correct format. I think the problem can be resolved if I can convert the text in my custom field into a date.


            I've been able to find a possible solution, but I'm having problems implementing this.

            If I run

            select to_date('03-Okt-2013','DD-Mon-YYYY','nls_date_language=NORWEGIAN') from dual;

            this returns the correct date in the correct format (03-OCT-2013), which could be saved in my table and the problem would be resolved. I know what language is being used in my APEX session so I've used the following code to my procedure:

                      CASE V('FSP_LANGUAGE_PREFERENCE')
                      WHEN 'no' THEN v_value := to_date(v_value,'DD-Mon-YYYY','nls_date_language=NORWEGIAN');
                      WHEN 'sv' THEN v_value := to_date(v_value,'DD-Mon-YYYY','nls_date_language=SWEDISH');
                      WHEN 'pl' THEN v_value := to_date(v_value,'DD-Mon-YYYY','nls_date_language=POLISH');
                      ELSE v_value := to_date(v_value,'DD-Mon-YYYY');
                      END CASE;

            I've tested this through SQL*Plus and it works perfectly. The dates are always converted from Norwegian to English (the default for the database) DD-MON-YYYY.


            However, when I run the same code through APEX it doesn't work. The date is still stored as Norwegian. If I enter 10-Mai-2013 in the field that is exactly what is stored in the database. I've even added debug to make sure it's running the right bit of code and it is.


            Why would this code work in SQL but not through an APEX session? And is there anything I can do to resolve this?!


            Many thanks


            • 3. Re: Date Picker Translate Help
              Mike Kutz

              Is your PROCESS doing the INSERT statement also?

              Or are you dependent on the Automatic DML?


              Did you add the value of V('FSP_LANGUAGE_PREFERENCE') to your debug?

              and does that correctly match your custom date picker's string value?


              When you get a chance, can you post an example on apex.oracle.com?

              • 4. Re: Date Picker Translate Help

                Hi Mike


                Yes my process does the insert as well. It uses apex_application.g_f0n(i) etc. to access the values of the custom fields on the page, which is the original value of v_value in my code. Before the bit of code above it gets the value from apex_application.g_f0n(i) and then after it inserts into my table.


                Yes my debug checked the value of FSP_LANGUAGE_PREFENCE and I even added a check to make sure it did the right bit of the case statement. It doesn't match my custom date pickers string value, but it doesn't need to. FSP_LANGUAGE_PREFENCE is determined from the browser settings and output on the page. This is then picked up by the date picker and used. As long as everything is refering to the FSP_LANGUAGE_PREFERENCE then everything will be consistent.


                I'm not sure I'll be able to set this up on apex.oracle.com due to the complexity. It involves custom javascript code and complex pl/sql code that my client would not want made available in this way. Sorry.


                I might be able to set up something a lot simpler to demonstrate, let me think about it...



                • 5. Re: Date Picker Translate Help
                  Mike Kutz


                  Do the INSERTs work?

                  Does the table use a DATE data type for that particular column?


                  This is starting to go beyond me.

                  I suspect that APEX handles displaying of dates as varchar2 values and (therefore) the displaying of a date as 1-may-2013 (instead of 1-mai-2013) for Norwegians is caused by the session's NLS_* parameter.


                  My reasoning:

                  APEX is nothing but PL/SQL code that runs inside the database.

                  The implicit to_char() function uses the NLS_* parameters (not V('FSP_LANGUAGE_PREFERENCES') ) to properly spell May as May in the US and Mai in Norway.


                  Check/mess with the applications properties for Globalization parameters on a demo app (designed to play with Dates in an international environment).


                  At worst, you should be able to set the NLS_DATE_LANGUAGE for the session by using Security->Database Session->Initialization PL/SQL Code:


                    v_lang varchar2(50);


                    -- set lang here

                    execute immediate 'alter session set nls_date_language=''' || v_lang || '''';



                  With the Cleanup PL/SQL Code resetting it to the DB's default of AMERICAN

                  execute immediate 'alter session set nls_date_language=''AMERICAN''';


                  I got this to work on apex.oracle.gov

                  I just haven't tested the 'sort' of the columns...



                  • 6. Re: Date Picker Translate Help

                    Hi Mike


                    Thanks for trying to help and apologies for not replying sooner (I've had a long weekend celebrating my little girls 1st birthday).


                    Unfortunately I don't think setting the nls_date_language in this way would help as I've tried using to_date(v_value,'DD-Mon-YYYY','nls_date_language=NORWEGIAN') which overrides the session nls_date_language.


                    My inserts do work but it doesn't (and can't) save to a date column. This is fine for the application as the values can never be displayed in a report, so sort ordering isn't an issue. Inserting in this way has shown that the values are not being translated to proper dates. When runing through sql*plus I always get 01-MAY-2013 as the value inserted whichever language I use, which shows that the value is converted to a date. But through APEX I get 1-Mai-2013 showing that it isn't.


                    It's really frustrating as I know that setting the nls_date_language works fine through sql. It's just through APEX that it's a problem.


                    I will play around some more and try to set something up on apex.oracle.com


                    Thanks again for your time and help.



                    • 7. Re: Date Picker Translate Help
                      Mike Kutz

                      SaraB wrote:

                      My inserts do work but it doesn't (and can't) save to a date column.

                      Are you storing DATE data in a varchar2(12) column instead of a DATE column?

                      If that is true, you need to stop working on APEX and get that corrected ASAP.

                      No excuses.


                      If you need help, let us know.



                      • 8. Re: Date Picker Translate Help

                        Hi Mike


                        I appreciate you're trying to help, but the response is a little rude.


                        I can't explain everthing in my application as the clients won't like that! But I've tried to give as much details as I can. This is a fairly minor area of the application, although very complex and it's frustrating that this is a problem.


                        The data I am storing might be date or might be text. The customer might set up lots of date fields, they might not set up any. But as I've tried to explain this is a problem regardless of the column type I am trying to save into. If I could save into a date field then I would, luckily from a functionality point of view it makes no difference but I appreciate it's not best practice.


                        My application uses text fields to save into a varchar2(4000) column (as it might also be a textarea). My problem is that APEX can change a text date into a DATE value in whatever language and I can't (even though the procedure works perfectly outside of APEX).


                        I'm in the process of setting up something to demonstrate this on apex.oracle.com, although I'm getting a different error here as APEX can't handle the different language. So having it as a date column definitely wouldn't make any difference.



                        • 9. Re: Date Picker Translate Help

                          Just to tie up this thread, I've managed to resolve my problem.


                          I've still no idea how APEX is doing what it's doing or why my process doesn't work when it's run from APEX. But if I save the value as DD/MM/YYYY format and convert it back to the format I want it in when it's displayed then this works fine. So from the code above I've simply changed:

                          WHEN 'no' THEN v_value := to_date(v_value,'DD-Mon-YYYY','nls_date_language=NORWEGIAN');


                          WHEN 'no' THEN v_value := to_char(to_date(v_value,'DD-Mon-YYYY','nls_date_language=NORWEGIAN'),'DD/MM/YYYY');

                          And changed the code that displays the field value to accomodate the change of format.


                          Hopefully this might help someone in the future. Although I think this is something quite specific to my clients requirements.



                          • 10. Re: Date Picker Translate Help
                            Mike Kutz

                            SaraB wrote:


                            My application uses text fields to save into a varchar2(4000) column (as it might also be a textarea).

                            With this statement, I am inferring that all of your data columns (date, numbers, strings, etc.) are varchar2(4000).

                            I really hope that this is not the case.

                            If so, this DATE problem is only the beginning of your nightmares.



                            • 11. Re: Date Picker Translate Help

                              Hi Mike


                              No, you're incorrect. This is the one and only place I store date information in a varchar2 column. As explained, it's a complex area of the system and if there were any other way of doing it I would.


                              I've been doing this a very long time, I don't make rookie mistakes like storing dates in varchar2 columns! Assumptions like you've just made is the reason I only use this forum when I really have to.



                              • 12. Re: Date Picker Translate Help


                                  I am sorry to jump into this thread like this, but DON'T you think you're being a little hard on Mike and possibly the WHOLE forum with that comment?  I am sorry, but people DO go out of their way to help STRANGERS with code issues here and all we ask is for some politeness and helpfulness.. 


                                Just my $.02 worth..


                                Thank you,


                                Tony Miller

                                LuvMuffin Software