Converting date to a date

In this article we will discuss what people end up doing erroneously. As data type Oracle stores DATE in a separate format then Character. But when passing values between two programs, the format may cease to be a Date anymore.

For example there are two independent systems, one which is a Oracle system (which we are going to see) and another foreign system which uses some other technology.

In this case let us also assume that the foreign system is our front-end to the user and Oracle system is the back-end part of the system. Say the front-end system is passing some date value which has been input from user to the database. The value will be interpreted as a Character by the Oracle system.

To convert the Character value to a Oracle-specific date data type, we will be using the function to_date(). The function to_date() accepts a Character value and will convert it into a Date (as understood by Oracle). We also needs to tell to the function what is the format in which the date value is coming. For example the character value 12-FEB-2007 can be converted to Date data type by using to_date('12-FEB-2007','DD-MON-YYY') function call.

Now what normally people do is that they do a conversion of a date data type to a date. That is a call to to_date() with date as input. For example: to_date(sysdate). What such a call will do is that it will implicitly convert the date parameter passed to a Character value, and then will pass it to to_date() function to be converted to a date again. The result is loss of time information. The time part will be truncated in this implicit conversion to Character.

Proof:

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
05.07.2007 13:42:00

SQL> select to_char(to_date(sysdate),'dd.mm.yyyy hh24.mi.ss') from dual;

TO_CHAR(TO_DATE(SYS
-------------------
05.07.2007 00.00.00

The moral learned is that never go for an implicit conversion. It is safe to use explicit data type conversions