But I want to store the date in format …..

This article has been moved to my new blog at edstevensdba.com.


Click here to go to the new location of this article.


9 thoughts on “But I want to store the date in format …..

  1. “Invariably, when people ask this type of question what they really want is to be able to display the date in their chosen format. ”
    Thanks for this explication.I came from MSSQL and i understand very hard oracle database concepts.

    Continue with these tutorials quality.We need them.

  2. “That means you will always do one of the following…”

    There is a third choice, in addition to the two you mention. If the SQL is being used to feed some kind of client tool – Forms, Reports, etc. – you should (probably) select the date field without setting its format, and set the format in the client tool.

    Good post though.

  3. Pingback: PL/SQL Developer Settings | Lalit Kumar B

  4. Hi Ed,

    I think it would be good enough to add the ANSI date literal syntax too as you have mentioned only TO_DATE. With ANSI DATE literal, there is some less typing 🙂

    • Lalit –
      Thanks for the comment. You just added it!
      The reason I didn’t mention it in the original article is, quite honestly, I simply wasn’t familiar with it at the time of writing. If I were writing the article again I’d probably add a paragraph or two about it, now that you mention it. But I could also make the argument that the purpose of the article was more about getting people to simply understand the difference between the DATE data type and a STRING data type that represents a date without actually being a DATE.

      Also, I just learned something else here. When you mentioned the DATE literal, my first thought was ‘isn’t that going to be NLS dependent’? So that forced me to double-check the docs before responding such, and I found that no, it is not NLS dependent, it is dependent on the string literal being in one fixed format.

      Actually, a lot of my developers use the DATE literal. They know they have to provide the date string the proper format. Well some of them understand. Others just do because that’s what they see in examples, without any understanding at all.

      • Thanks for the response. I like your humour 🙂 To be honest, let me share my experience:

        You know how it feels when a college grad who just joined the firm as an intern, asks you, Oh! you don’t know the new ANSI date literal? And I was like, “what ANSI literal? TO_DATE, that’s it, live with it”. I now have that intern in my team(of course, as a full time employee), working with a team responsible for sorting out customer issues related to Oracle’s calendar syntax.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s