



How does this happen?

The datatype is called ‘Date’





When they query it, they only see the date




Make it your job to coach the new folks



How to change the default DATE display behavior in SQL Developer

Open the preferences, and search for ‘NLS.’





Configuring SQL Developer to display Time Component of a DATE



  1. Avatar
    Manuel Guillén Reply

    Excellent!! Thanks!! that info about nls preferences help me a lot.

  2. Avatar
    Tomkin Lee Reply

    If this works

    why does

    fails with
    ERROR: object PS_ACA_PER_ELIG does not exist

    Is it permission/security related?

    • thatjeffsmith

      is that a synonym to an object in another schema or a local object?

      what version of SQLDev are you using?

    • Avatar
      Tomkin Lee

      The table is a PeopleSoft delivered table and local object.

      SQL Developer ver Build Main 10.20

      Another thing I noticed is that I am unable to open/expand the folder to display all the tables which kinda leads me to think it may be a configuration related issue.

    • thatjeffsmith

      So how often do we run into this before we get so upset we have to blog about it? For me it was about 300x or so, give or take 10,000.

  3. JeffS

    >> when relying on Oracle for implicit type conversions
    Never! Don’t leave it up to chance I say. Of course you said the code is delivered, so I understand it’s out of your hands.

    Have you tried using the extract() function to pull out the different elements of an interval and then feeding that output to your analytic and aggregate functions? I have to admit I haven’t tried what you’re talking about, but I’d be happy to investigate a better solution for you if you could give me an example.

    Thanks for the backup Dave! I was getting killed on Twitter this morning 🙂

  4. Avatar

    Can’t agree with you more on the Date data type issue. I always make it a habit of changing my NLS_DATE_FORMAT to include 4 digit years and the time component. Of course this can cause problems when working with delivered code that fails to provide proper date masks when using the to_char() and to_date() functions or when relying on Oracle for implicit type conversions which no longer match the expected value.

    As for INTERVALS they are one of my favorite data types. Unfortunately they are not terribly well supported by oracle. The to_char() function ignores masks when used with an interval, there are no corresponding to_[ym|ds]interval() functions to convert strings to intervals. There are the numto[ym|ds]interval() functions. My biggest gripe though is that analytic and aggregate functions don’t work with intervals.

  5. Avatar

    Personal pet peeve of mine with Oracle is that the DATE data type is more akin to TIMESTAMP. However, that being said, the ability to manipulate date values, ahem, timestamp values in Oracle with simple arithmetic instead of INTERVALS is very nice.

    Good tip on changing the NLS settings in the tool to reflect what is really being returned. Thanks!

    • JeffS

      Well, you had me until you mentioned TIMESTAMP. TIMESTAMP allows you to define a time down to the fraction of a second. Which is very important if you start to look at things like stock trades. I would use Date whenever it’s OK to just know the date and time of day down to the second.

      Funny that you mention INTERVALs. I just recently took the time to figure out how those exactly worked because for some reason they had always seemed foreign to me.

Reply To Dave G. Cancel Reply

Follow by Email