TO_DATE or not TO_DATE

Like a middle aged man visiting the gym for the first time in 10 years, I am merely going to flex my blogging muscles here…

One of the recurring issues that annoys me on the OTN SQL & PL/SQL forum, and also the APEX forum is the misunderstanding of what the TO_DATE function actually does. I think many people that are new to database development and Oracle see TO_DATE and it’s format mask and think – great I can reformat a date!

Well, no. That’s not what it does and is not what its for. Let’s get back to basics.

A date is a date. It’s not ’14-03-2013′, ’03-14-2013′, ’14 Mar 2013′ or any other representation you can think of. It’s a date, and in Oracle’s world, a time too. The moment that you are reading this is a date.

A string (or char or literal or whatever you want to call it – this post will use string from now on) is a series of characters. ‘Ben’ is a string, in the same way that ’14 March 2013′ is a string, they mean very little to the database unless they’re relating to something else.

So, to the purpose of TO_DATE()!

TO_DATE() is a system function that allows you to describe to the database a date that you are passing in as a string. The conversation would go something like this…

Developer: Yo DB! Got an input here, it’s ’14-MAR-2013′ – knock yourself out.

DB: Cool! I like strings.

Developer: But it’s a date?

DB: Oh. You didn’t mention that. What date is it?

Developer: I have to explain everything? You just can’t get the staff these days! It’s in DD-MON-YYYY format obviously…

DB: You should have just said, no need to be abusive.

In code, that would be TO_DATE(’14-MAR-2013′,’DD-MON-YYYY’). The database now knows it’s a date and can treat it as such. The Oracle doc’s and a million blog posts will tell you the many forms in which you can ‘describe’ the date to the database. So, that is TO_DATE(), and that’s what it does.

So why do people get confused? Well, mostly because they see the term ‘format mask’ and they’ve heard of TO_CHAR(). Then think the two functions can be used interchangeably because they both have ‘format masks’.

TO_CHAR() is a system function that allows you to describe to the database how to return something as a string. The conversation would go something like this…

Developer: Yo DB! Got a date here, just pop it back to me as a string so the user can see it please.

DB: Cool! Here you go – ’04-03-2013′

Developer: The user’s American?

DB: Oh, you didn’t mention that. How do they want it?

Developer: I have to explain everything? You just can’t get the staff these days! It’s in MM-DD-YYYY format obviously…

DB: You should have just said, no need to be abusive.

In code, that would be TO_CHAR(the_date,’MM-DD-YYYY’).

By now you probably wondering why the Developer keeps saying ‘Yo’ and the DB keeps replying with ‘Cool!’. That there is implicit conversion. The DB *knows* that the developer is a 1980′s pop rap singer, so it knows how to respond. This is all in your NLS parameters, there you can set the default date format for the database. This is why many people come across the situation of “this always works but suddenly the results are wrong!” when dealing with a predicate like…

my_date_column = :p_myDate

Now, let’s say the NLS parameters are set to ‘DD-MM-YYYY’. All the users input dates in that format. Then they employ an American and they input a date as ’03-04-2013′. The DB thinks that this is 3rd April 2013, but the user meant 4th March 2013.

You should never rely on implicit conversion (the same applies to numbers here), you should ALWAYS describe to the database explicitly what you are giving it.

Note to APEX developer’s here – every item that you have is returned as a string, so all of your reports etc. that refer to items as bind variables should be explicitly converted if they are supposed to be numbers or dates – saying “but it works at the moment” is not good enough!. APEX does do some clever things around this but I would always convert explicitly in my code.

Why does this all annoy me so much? Well, I see people doing nested TO_CHAR(TO_DATE(TO_CHAR())) all the time in the forums and wondering why their code isn’t working. Just remember – TO_DATE() describes to the database that you are giving it a date, TO_CHAR() can allow you to return a date as a string in a specified format. Stop being abusive to your database!

Okay, so that’s the blogging hiatus finished – I’ll be back soon!

About these ads

2 thoughts on “TO_DATE or not TO_DATE

  1. Buzz Killington says:

    Good post. I find that dates is the hardest thing to explain to people learning Oracle. Before I get into to_char and to_date, I make a very rudimentary explanation that Oracle stores dates (any column with a date datatype) in its own internal format. When you query and get back 14-MAR-2013, you are seeing a character representation of the internally stored date. Oracle has already done an implicit to_char for you.

    So if you want to convert an internally stored date to a specific format, use to_char. If you are putting data into the database in a date column, to_date will convert your string into the internal format.

    No matter how many times I’ve explained it, it still eludes a lot of people. I will often go into Excel and show them how Excel will turn a date into some integer as the representation. Then it sometimes clicks.

    As you mention too, people don’t get it because of the implicit conversions Oracle does for you. So sometimes it works without a to_date automatically and they are confused when it suddenly stops working b/c they have a different string format.

  2. Scott says:

    Yo, nice rant :-)

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s