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!

ExtJS and cliffs…

Okay, I haven’t posted in a very long while…

So, apologies for the delay, but such is the result of falling off a cliff!

I have mentioned a few times that I was working on some ExtJSy stuff and some people have asked me when it will be ready. Quick answer is not very soon but I would welcome you to have a look at where I’m at so far.

I took great inspiration from Mark Lancaster (I do hope you’ve seen his site), and Matt Nolan at e-DBA. The route I took was to use a PL/SQL package to dynamically (probably strecthing the use of the word there…) create the ExtJS scripts based on the meta data from the APEX tables and a bit of DBMS_SQL, a few application processes and a couple of standalone JS files in the header of the page template. It’s all pretty clean and generic.

On the home page there’s a little portal kind of thing where the positions of things are based on the region positions, columns etc. If you find the page with some grids on it – it uses the alignment, default sort settings etc. from the APEX data as well. So you just enter the report query and settings as normal in APEX and the PL/SQL package picks all of that up to create the grid.

It’s just a starting point at the moment and something I hope to work on when in finer form.

So sorry for the wait and sorry it’s only just started but here’s the link…

Munky’s Zoo

Please e-mail me with suggestions for what to do next…