SYSDATE is one of the most commonly used Oracle functions. Indeed, whenever you need the current date or time, you just type SYSDATE and you’re done. However, sometimes it’s not all that simple. There are a few confusions associated with SYSDATE that are pretty common and, if not understood, can cause a lot of damage.

First of all, SYSDATE returns not just current date, but date and time combined. More precisely, the current date and time down to a second. If just a date is needed, TRUNC function has to be applied, that is, TRUNC(SYSDATE). For a sake of a good database design, date should not be confused with date/time. For example, if a column in a table is called transaction_date, it would be natural for it to contain a date, but not date/time. That may lead to a major confusion. Let’s imagine there is a table BANK_TRANSACTIONS containing the following fields:

txn_no     INTEGER,
txn_amount NUMBER(14,2),
txn_date   DATE

The last field is of the most interest to us. Apparently its data type is DATE, but is it a date or date/time? We can’t tell by just looking at the table definition. Nonetheless, it is a very important thing to know. A common case for using DATE columns is including them in date range queries. Forexample, if we wanted to get all the bank transactions from 1 January 2009 to 31 July 2009 we could write this:

SELECT txn_no,
       txn_amount
FROM   bank_transactions
WHERE  txn_date BETWEEN To_date('01-JAN-2009','DD-MON-YYYY')
AND    To_date('31-JUL-2009','DD-MON-YYYY')

And that would be fine if TXN_DATE were a date column. But if it is a date/time, we would just have missed a whole day worth of data. And it is because, as I said, DATE data type can hold date/time down to a second. That means that for 31 July 2009 it could hold values ranging from 0:00am to 11:59pm. But because TO_DATE('31-JUL-2009', 'DD-MON-YYYY') is basically an equivalent to TO_DATE('31-JUL-2009 00:00:00', 'DD-MON-YYYY HH24:MI:SS'), all the transactions happened after 0:00am on 31 July 2009 would be missed out.

That kind of mistake is pretty common. Sometimes it’s hard to tell by just looking at the data whether a particular DATE column can have date portion. Even if all the values in there are rounded to 0:00 hours, that doesn’t mean that a different time value can’t appear there in the future. The data dictionary can’t help us here either – DATE type is always the same whether it contains time or not. (By the way, Oracle recommends using TIMESTAMP type for new projects, but that is a whole different story.)

If you are working with an existing table and you are not sure, you can use a fool-proof method like this:

SELECT txn_no,
       txn_amount
FROM   bank_transactions
WHERE  txn_date BETWEEN To_date('01-JAN-2009','DD-MON-YYYY')
AND    To_date('31-JUL-2009','DD-MON-YYYY') + 1 – 1/24/3600

+1 – 1/24/3600 here means “Plus 1 day minus 1 second”. That is because “1” in DATE type means “1 day”, “1/24” - 1 hour, and there are 3600 seconds in an hour.

The above expression will retrieve all the transactions from “01 January 2009 0:00am” to “31 July 2009 0:00am plus 1 day minus 1 second”, i.e. to “31 July 2009 23:59pm”.

If you are charged with designing an application and need to create a table with a DATE column, it is worth to keep yourself and others from future confusions by a simple trick: name columns that only contain date portions as DATE and add TIME to the name of the columns that you know will contain time components. In our case it would be prudent to call the date/time column TXN_DATE_TIME.

The second issue I’d like to discuss is much more subtle, but can do even more damage.

Imagine that you are charged with developing a report that returns all the transaction for the previous month. It looks like a job for SYSDATE! You fetch your trusty keyboard and after a few minutes of typing you come up with something like this:

SELECT txn_no,
       txn_amount
FROM   bank_transactions
WHERE  txn_date BETWEEN Last_day(Add_months(Trunc(SYSDATE),-2)) + 1
AND    Last_day(Add_months(Trunc(SYSDATE),-1))

You create a few lines in BANK_TRANSACTIONS table, run a few unit tests to make sure your code works and check it into the source control. Job done! You congratulate yourself on the productive work and spend the rest of the day reading your friends' blogs and dreaming about your next vacation. And the next day you move on to another task and get as busy as ever.

After some time, which may be a few days or months, depending on the pace of the project, the code you wrote gets migrated into the UAT environment. And a task force of a few testers and end users is assigned to test the report you wrote. And as it often happens in UAT, they are going to test in on real data they extracted from the production system – that is, the last year’s data.

Got it? Last year’s.

The final stages of testing, such as UAT, have to prove that the system does what it is expected to do in conditions that resemble the production as closely as possible. And the best way to do that is to test it on the retrospective production data – the data that is proven. That makes it possible to compare the outcome to the actual production system, and thus, prove or disprove that the new system works.

That sounds reasonable. But one of the implications for you is that BANK_TRANSACTIONS table is not going to contain previous month’s transactions. Hence, your report will be blank. You can’t rewind back time because you hard-coded SYSDATE, which has only one meaning – “right now”. Test failed.

If you have known that when you wrote it, you wouldn’t have used the SYSDATE. You would use a parameter, something like v_run_date, which you could set to whatever date you wanted. And that would do. Well, now you know.

It’s a follow-up to the previous post.

As it turned out, implicit date conversions may also prevent Oracle from doing the partition pruning. For example, if you have a table INVOICES with a range partition on INVOICE_DATE field, then expression

SELECT
...
WHERE invoice_date >= '01-MAR-09'
  AND invoice_date <  '02-MAR-09'

will not perform the partition pruning, whereas

SELECT
...
WHERE invoice_date >= TO_DATE('01/03/2009', 'DD/MM/YYYY')
 AND invoice_date <  TO_DATE('02/03/2009', 'DD/MM/YYYY')

will.

Because the efficiency of partition pruning is usually why partitioning is used in the first place, the choice is obvious.

But after all, I’d use

SELECT
...
WHERE invoice_date BETWEEN TO_DATE( '01/03/2009', 'DD/MM/YYYY')
AND TO_DATE( '01/03/2009', 'DD/MM/YYYY') + 1 - 1/24/3600

since BETWEEN operation is specifically tailored for such situations. “1/24/3600” here represents 1 second, and the whole statement should be read as “From 01 March 2009 0:00am to 01 March 2009 11:59pm”.

When I was going through PL/SQL procedures written by some of my colleagues, I noticed a few mistakes made around the Oracle’s date conversion functions. There are some peculiarities about those functions that I thought everyone knew about. But I reckon if I write about it, it may help others to avoid such mistakes. I also allowed myself to outline a few rules that, if you adhere to them, will help you to write better programmes.

There are 2 functions in Oracle to convert strings to dates and back.

The first one is TO_DATE – it takes a string parameter and returns a date. Ok, it’s actually adate/time combination enclosed into a single data type – Oracle’s DATE.

The second one is TO_CHAR – does the opposite: it takes date/time as Oracle’s DATE data type and converts it to string.

Actually, these functions are bit more complex than that, but for our purpose that will do. What’s important to understand here is the distinction between date as a DATE data type and its string representation.

For example, when you type ’01-APR-09’ in the procedure’s text, that’s a string, representing a date. Pay attention here: although you meant a date, Oracle sees a string. For Oracle everything that is enclosed in single quotation marks is a string. To make it a date, we need to convert this string to a DATE data type. Such conversion can be carried out by 2 possible ways: explicitly and implicitly.

Explicit conversion is when we apply the TO_DATE function to the string:

v_date DATE;
v_date := TO_DATE('01/04/2009', 'DD/MM/YYYY');

Now v_date is a date, representing April 1st, 2009.

Implicit conversion is when we let Oracle to perform the conversion:

v_date DATE;
v_date := '01-APR-09';

It has the same effect. Every time Oracle sees a string in place where it expects a date, it is smart enough to perform the conversion for us. “Well”, you may think, - “That’s great. Oracle does it all for us, so we don’t have to do it. Life is easier, let’s go for another coffee break”.

Not quite.

You see, when Oracle does such implicit conversion, it relies on some assumptions. If you read the documentation for TO_DATE and TO_CHAR functions, you’ll find that they take another optional parameter – the date format. That format tells Oracle how the string representing the date/time should be treated. If the format parameter is not specified, it is taken from NLS_DATE_FORMAT Oracle parameter. Here’s the crux: We can’t assume that this parameter will be the same on all Oracle systems. Although it is ‘DD-MON-RR’ by default and it is left like that on most Oracle systems, we can’t assume that it’s going to be like this always and everywhere. And if you rely on implicit date conversions and some DBA changes NLS_DATE_FORMAT parameter – WHAM! – All your programs will stop working.

So, a good practice and rule of thumb for you should be:

Never ever rely on implicit date conversions! Whenever you need to convert date to string or vice versa, use an appropriate TO_DATE or TO_CHAR function and always specify a date format.

Just like this:

v_date DATE;
v_date := TO_DATE('01/04/2009', 'DD/MM/YYYY');

The danger of NLS_DATE_FORMAT being changed is the biggest threat but not the only one.

Pay attention to the default date format I provided just above – ‘DD/MM/RR’. Do you notice anything suspicious? The year is 2 digits. Here Oracle tries to be smartass and tries to guess whether you mean XX or XXI century. Your only hope that it can figure out what you meant and doesn’t make a mistake. But if it mistakes – oops, welcome back the Millennium Bug. This brings us to the second rule:

Always specify the 4-digit year.

Another dangerous programming technique is trying to convert Date to Date where no conversion is necessary.

Let’s have a look at the following example, or should I say a puzzle?

DECLARE
   v_date DATE := '01-APR-09';
   v_date_2 DATE := TO_DATE (v_date, 'DD/MM/YYYY');
BEGIN
   dbms_output.put_line (TO_CHAR (v_date_2, 'DD/MM/YYYY'));
END;

Try to guess what will be printed as a result.

If you think ‘01/04/2009’, you’ve just screwed your business critical application and have sent it two thousand years back in time.

In fact, you’ll get ‘01/04/0009’.

This is where it all goes bad:

v_date_2 DATE := TO_DATE (v_date, 'DD/MM/YYYY');

And here’s why:

The first thing Oracle tries to do is to execute TO_DATE function. There is only one TO_DATE function in Oracle – the one that takes a string and converts it to a date. Despite we know that v_date is not a string, Oracle still proceeds with its logic. If you run this code, it won’t produce an error.

Oracle successfully convinces itself that it sees a String where it has a Date. That happens because Oracle is able to implicitly convert that date to a string, effectively turning that line into

v_date_2 DATE := TO_DATE (TO_CHAR(v_date), 'DD/MM/YYYY');

But, as we’ve already learned, implicit date to string conversions are performed using the date format recorded in NLS_DATE_FORMAT Oracle parameter, which is by default set to ‘DD-MM-RR’. Hence, what Oracle effectively does is this:

v_date_2 DATE := TO_DATE (TO_CHAR(v_date, 'DD-MM-RR'), 'DD/MM/YYYY');

Can you spot the error already? The date formats are inconsistent! This is what you get when you don’t pay attention to the details.

So, here comes rule 3:

Avoid unnecessary conversions. Never convert dates to dates.

If you think that all this stuff is pretty confusing, that’s because it indeed is. The good news is that you can avoid the confusion altogether by learning to program in a more clear, more concise way. That is a foundation of a good programming style.