14 Replies Latest reply on Feb 26, 2014 5:26 PM by rp0428

    See date+time in date cells without modifying NLS settings

    Arpod

      We have a lot of date columns containing date+time values, which I often need to see while browsing table data.

      Default NLS settings for date format are dd.mm.yyyy for 'Russia', so our DB uses this by default, which is in turn used by SQLDeveloper. So, to see the time values, I should either write select with appropriate to_char manually, or change NLS settings under "Database" in the preferences. Of course, changing NLS setting seems preferable.

       

      However, changing NLS date format has certain side effects. For example, it affects various tasks, mainly involving exporting, which are launched from SQLDeveloper, because default to_char format for date values also changes. Of course we could blame lazy developers for not specifying formats explicitly, but it doesn't change the fact that the results of task execution are "wrong", and that can be rather hard to notice until subsequent tasks fail.  Also, if I'm not mistaken, NLS_DATE_FORMAT also affects to_date default format, making import from text and querying date columns rather annoying. It's better to be safe than sorry, so I have to keep NLS settings

       

      So, is that possible to set date format only for displaying the values in data grid, without affecting NLS settings in any way? I'm fine with pretty much anything, including writing an extension myself if necessary, I just need it to work. If it's impossible, could SQL Developer team please add such setting, or at least show a tooltip on mouseover, displaying 'full' cell value?

        • 1. Re: See date+time in date cells without modifying NLS settings
          thatJeffSmith-Oracle

          >>so our DB uses this by default, which is in turn used by SQLDeveloper

          SQL Developer uses the NLS_DATE format stored in Tools > Preferences

           

          • 2. Re: See date+time in date cells without modifying NLS settings
            Arpod

            >SQL Developer uses the NLS_DATE format stored in Tools > Preferences

             

            I assumed that defaults for those values are derived from default DB settings, are they not?  Either way, changing these settings gives me all the side effects I mentioned in the OP...

            • 3. Re: See date+time in date cells without modifying NLS settings

              No - those defaults are not derived from the database settings.

               

              Yes - there is only ONE set of preferences in sql developer so changing them will affect everything that sql developer does.

              1 person found this helpful
              • 4. Re: See date+time in date cells without modifying NLS settings
                thatJeffSmith-Oracle

                Yeah, I'm not understanding what you mean by side effects. Can you give some examples of how you want your DATES to be formatted?

                 

                You can also change you nls params per query/script by using an ALTER SESSION SET...but that seems excessive.

                • 5. Re: See date+time in date cells without modifying NLS settings
                  Arpod

                  rp0428

                   

                  Just as I thought... Is there any other way to change how grid displays dates, via extensions/API/manual properties modification?

                   

                  thatJeffSmith-Oracle

                  >Yeah, I'm not understanding what you mean by side effects. Can you give some examples of how you want your DATES to be formatted?

                   

                  I want my dates displayed as 'DD.MM.YY HH24:MI:SS', but I DON'T want that setting to affect default to_char format or exporting. Basically, I want a setting to change visual representation of values in the grid (under table->data, for example), which won't actually affect NLS.

                  • 6. Re: See date+time in date cells without modifying NLS settings
                    thatJeffSmith-Oracle

                    NLS is the only way to affect display of dates in the grids. To_char formatting is up to you though - it will use what you define in the function call.

                    • 7. Re: See date+time in date cells without modifying NLS settings
                      Arpod

                      Well, too bad. Though, tooltip would be really helpful in this case, please consider implementing it in some future releases.

                      • 8. Re: See date+time in date cells without modifying NLS settings
                        thatJeffSmith-Oracle

                        I still don't understand what you're trying to accomplish.

                        • 9. Re: See date+time in date cells without modifying NLS settings

                          Ok - there still seems to be some confusion.

                          I want my dates displayed as 'DD.MM.YY HH24:MI:SS', but I DON'T want that setting to affect default to_char format or exporting.

                          1. There is a 'server' (the Oracle database) and a 'client' (sql developer)

                           

                          2. The 'server' does NOT format ANY columns of the projection (the result set) unless you specifically include formatting in your query. So there IS NO default to_char format applied by the server

                           

                          3. ALL formatting of result set columns is performed by the client. Jeff already told you that for dates this is controlled by the NLS preferences that are set in sql developer. Those settings are separate and distinct from the NLS settings in the database. For sql*plus the NLS settings of the database will be used if you do not specify a format using the FORmat setting.

                           

                          4. The sql developer NLS preference settings WILL BE used if you export data. Set the preference value to 'yyyy' (all lowercase) and an exported row will look similar to this:

                          Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'new_SMITH','CLERK',7902,to_date('1980','yyyy'),30,null,20);

                          5. Change the preference value to 'YyYy' (mix of upper and lower case) and it will look slightly different

                          Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'new_SMITH','CLERK',7902,to_date('1980','YyYy'),30,null,20);

                          Note that the format string in the embedded TO_DATE call is EXACTLY as specified in the preferences.

                          Basically, I want a setting to change visual representation of values in the grid (under table->data, for example), which won't actually affect NLS.

                          Hence the confusion - those settings will NOT affect the 'server' but WILL affect the 'client'.

                           

                          You seem to be wanting a way to specify a different sql developer preference for exports than the preference used for data grids.

                           

                          If so, you would likely need to submit an enhancement request.

                          • 10. Re: See date+time in date cells without modifying NLS settings
                            Arpod

                            You seem to be wanting a way to specify a different sql developer preference for exports than the preference used for data grids.

                            Yes, sort of. I'll try my best to explain what I want once again:

                             

                            There's a 'server' date format NLS setting, 'DD.MM.YYYY', the default SQLDeveloper NLS preference (under Tools->Preferences), which also happens to be 'DD.MM.YYYY', and the one I want to see in data grid - 'DD.MM.YYYY HH24:MI:SS'.

                             

                            I understand that changing SQLDeveloper NLS preferences does not affect server-side settings, so whatever I set there will not affect other users.

                             

                            However, there is some PL/SQL code in our DB, where to_char(date) is called without second argument, relying on whatever current NLS settings are. I know this is terrible practice, and I try to fix it wherever I see it, but it "works" for all other users, so there's no knowing when and where I encounter it again. So, if I (unknowingly) call functions/procedures containing this "to_char" directly from SQLDeveloper, after changing SQLDeveloper NLS preference, it ends up using 'DD.MM.YYYY HH24:MI:SS' format, instead of 'DD.MM.YYYY' that was intended by previous developers. And as I said before, that could be hard to notice until some subsequent tasks, which expected 'DD.MM.YYYY' format in their data, fail. It can take hours to regenerate those data sets, and I just don't want to go through it again.

                             

                            Additionally, if I do export to text, XLS, csv files or whatever, I would expect to see dates in 'DD.MM.YYYY' format unless explicitly specified otherwise. In other words, I need a preference that would apply  'DD.MM.YYYY HH24:MI:SS' format to data grids, but keep 'DD.MM.YYYY' for everything else. A tooltip that appears on moving mouse cursor over the date cell, showing its date+time value, would also suit my needs.

                             

                            I already understood that no such setting exists, and that it's fairly specific; however, as I know that SQLDeveloper provides certain extension API, I wondered if it's possible to implement something like this myself, using something exposed by dev. team? I can handle Java programming well enough, I just need to know if there's any sort of interface I can use and a pointer to appropriate Javadoc.

                            • 11. Re: See date+time in date cells without modifying NLS settings
                              thatJeffSmith-Oracle

                              Yeah, you need to fix your code - which you know. And we can help you find it.

                               

                              Use the View > Find DB Object and search the source code for where there are to_char() calls. You can probably use a regular expression to find where there's no 2nd argument.

                               

                              If you want to export your your data w/o the time portion, just query it such, and it will export exactly as you want.

                               

                              The odds of us adding a preference here are slim to slimmer.

                              • 12. Re: See date+time in date cells without modifying NLS settings

                                Now I think it is YOU that is confused.

                                However, there is some PL/SQL code in our DB, where to_char(date) is called without second argument, relying on whatever current NLS settings are. I know this is terrible practice, and I try to fix it wherever I see it, but it "works" for all other users, so there's no knowing when and where I encounter it again.

                                Correct - that code will rely on the database PLS settings to convert that 'date' value to a string. And, as you say, you should NOT rely on implicit conversions in your queries.

                                 

                                But that use of TO_CHAR in your PL/SQL code has NOTHING to do with the issue in your thread.

                                So, if I (unknowingly) call functions/procedures containing this "to_char" directly from SQLDeveloper, after changing SQLDeveloper NLS preference, it ends up using 'DD.MM.YYYY HH24:MI:SS' format, instead of 'DD.MM.YYYY' that was intended by previous developers.

                                Sorry - I just don't see how that is even possible.

                                 

                                The TO_CHAR function returns a string. Sql Developer does NOT use ANY conversions on strings such as you describe. Sql developer does those conversions on DATE values that is displays/exports as strings.

                                • 13. Re: See date+time in date cells without modifying NLS settings
                                  Arpod

                                  The odds of us adding a preference here are slim to slimmer.

                                   

                                  Yeah, I understand that. Though I would still like to know if there's any sort of API to extend it myself, if possible.

                                   

                                  Sorry - I just don't see how that is even possible.

                                   

                                   

                                   

                                  The TO_CHAR function returns a string. Sql Developer does NOT use ANY conversions on strings such as you describe. Sql developer does those conversions on DATE values that is displays/exports as strings.

                                   

                                  Sorry, but you are wrong. You can test that yourself - change NLS Date setting in the preferences, then run either of the following:

                                   

                                  select 'Current date is: '||to_char(sysdate) from dual;
                                  
                                  
                                  

                                   

                                  create or replace function to_char_implicit(convert_me date) return varchar2 as
                                  begin
                                    return 'Converted date is '||to_char(convert_me);
                                  end;
                                  /
                                  select to_char_implicit(sysdate) from dual;
                                  
                                  
                                  

                                   

                                  What I got after setting 'DD.MM.YYYY HH24:MI:SS' in the preferences was:

                                   

                                  'CURRENTDATEIS:'||TO_CHAR(SYSDATE)
                                  ----------------------------------
                                  Current date is: 25.02.14 11:40:18
                                  
                                  
                                  

                                  and

                                  FUNCTION TO_CHAR_IMPLICIT compiled
                                  TO_CHAR_IMPLICIT(SYSDATE)
                                  ----------------------------------
                                  Converted date is 25.02.14 11:40:18
                                  
                                  
                                  

                                   

                                  If I change preferences back to 'DD.MM.YYYY'  I get:

                                   

                                  'CURRENTDATEIS:'||TO_CHAR(SYSDATE)
                                  ----------------------------------
                                  Current date is: 25.02.14
                                  
                                  
                                  

                                   

                                  FUNCTION TO_CHAR_IMPLICIT compiled
                                  TO_CHAR_IMPLICIT(SYSDATE)
                                  ----------------------------------
                                  Converted date is 25.02.14
                                  
                                  
                                  

                                   

                                  So NLS Preferences clearly affect implicit to_char conversion. I guess SQLDeveloper NLS preferences affect current session settings or something.

                                   

                                  UPD: no idea how to get rid of those spans around code blocks, they aren't in html, sorry.

                                  • 14. Re: See date+time in date cells without modifying NLS settings
                                    I guess SQLDeveloper NLS preferences affect current session settings or something.

                                    I believe you are correct - it is changing your session settings.