3 Replies Latest reply: Mar 24, 2014 9:17 AM by Jeff Smith Sqldev Pm-Oracle RSS

    Hide the time if not present in date columns

    Vincent Botteman

      Hi,

      In the NLS preferences of SQL Developer 4.0.1 I changed the date  format to DD/MM/YYYY HH24:MI:SS to see the time. But in our database, some date columns only contain dates without a time portion. In that case SQL Developer shows the value as 22/03/2014 00:00:00. How can I hide the time portion (00:00:00) in that specific case?

      Regards,

      Vincent Botteman

        • 1. Re: Hide the time if not present in date columns
          rp0428

          In the NLS preferences of SQL Developer 4.0.1 I changed the date  format to DD/MM/YYYY HH24:MI:SS to see the time. But in our database, some date columns only contain dates without a time portion. In that case SQL Developer shows the value as 22/03/2014 00:00:00. How can I hide the time portion (00:00:00) in that specific case?

          What database are you talking about?

           

          Oracle DATE values ALWAYS have 'a time portion'. That time may be 00:00:00 but it is a time value nonetheless.

           

          So no - you can not specify one preference for some values and a different preference for others.

          • 2. Re: Hide the time if not present in date columns
            Galo Balda

            Vincent,

             

            What rp0428 said is correct. Oracle stores date and time. You may be asking because some other tools (PL/SQL Developer is one of them) show  22/03/2014 00:00:00 as 22/03/2014 by default, but that's not possible in SQL Developer.

            • 3. Re: Hide the time if not present in date columns
              Jeff Smith Sqldev Pm-Oracle

              Things you CAN do:

               

              1. configure NLS Date Format to now show time, DD-MON-RR for example
              2. if time is INDEED not relevant, then create a view where the date columns are reformatted using TO_CHAR(column, DATE_FORMAT) from table;

               

              But yeah, all dates have times, even if you don't care about them. When you train users to think they aren't there, bad things will happen.