ThatJeffSmith

Oracle Date Datatype Should be DateTime DataType

Want to confuse an Oracle database n00b? Ask them if their query takes into account the time component of a date when they code their WHERE blahDate between … AND … logic.

But sir, my dates do not have a time associated with it. You must be confusing this with TimeStamp. See, that has the word ‘time’ in it, so that is where Oracle keeps time stuff.

It’s at this point that I seriously start to doubt the reliability of any of the code this person has written.

How does this happen?

The datatype is called ‘Date’

What’s today’s date? Oh today is February 10th, 2012 & 9:39:25 in the AM, Eastern Standard Time. Dude, I didn’t ask for the time, I asked for the date!

Let’s check out line numero uno of the Oracle Docs description of the DATE datatype:
“The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).”

Well that’s pretty straightforward. Surely when n00b starts their career in IT, they read all the manuals first? I mean, it’s not like they’re this guy?

We need to make sure Robert Culp is there to catch the n00b when he tries to fly and can’t figure out where or how to land!

When they query it, they only see the date

The n00b’s misconception is further strengthened when they start querying fields that are stored as DATEs. Let’s take a quick look at this phenomena in SQL Developer:

Look ma, no time! Oh wait a second...

So why the ‘no time’ in SQL Developer? We need defaults, and showing a list of dates that include a time component of 00:00:00 takes a lot of real estate in the data grid. So we compromised. And it’s probably OK MOST of the time. But –

Make it your job to coach the new folks

When ‘new guy’ or ‘new gal’ starts on the job, offer to do code reviews. If they resist, make it an offer they can’t refuse. Learn how to give truly CONSTRUCTIVE criticism. Be prepared with examples and stories of how YOU cost the company $27,123 back in the day because you were neglecting sales that occurred after midnight.

Even better, offer to host a lunch and learn! Buy a pizza and a 12-pack of soda (never beer!) and give a quick 20 minute overview the topic at hand.

How to change the default DATE display behavior in SQL Developer

Open the preferences, and search for ‘NLS.’

The defaults that are showing on my install are based off of a my local OS. I’m in America, so it’s showing as such. If I were in China, we’d be seeing Chinese settings most likely.

Simply add the time component to the NLS_DATE_FORMAT setting in SQL Developer and you’ll see the time component whenever you query a DATE field…even if it’s 00:00:01!

OR

If you want to have SQL Developer just show the data as the database intends based on the NLS_DATABASE_PARAMETERS, then tell SQL Developer to ‘Skip NLS Settings.’

Configuring SQL Developer to display Time Component of a DATE