A date show in Oracle

This Blog will help you to understand how Date are stored in Oracle database and how it can be retrived as a character in any format.

First of all, the basics. Open SQL*Plus and give the following SQL statement.

select sysdate from dual;

This statement will return depending upon the date settings. In my system it returned

sysdate
--------
14-Jul-2005

This is what Oracle knows what a date is. That is speaking more clearly, without a format date have the 'dd-mon-yyyy' format in Oracle. Oracle always stores date values in the above format if the session date format is not changed.

To select some other formats, you must specify

select to_char(sysdate,'dd/mm/yyyy') from dual;

to_char(sys
------------
14/07/2005

The above select statement has a major difference with the first one. In the first one Oracle returned date datatype whereas in second we converted it to character and Oracle returned as per my requirement.

But what if I am displaying it in this format but I want to store it in some other date field?

There arises the problem of converting again the character into date. This is possible using the to_date() function.

But I am going to illustrate the major difference of using database's default format and an other one here.

If i want to get the output again in date, normally it is enough for me to give the following statement.

select to_date(to_char(sysdate,'dd/mm/yyyy')) from dual;

But this will give me an error. Because if you do not specify any format mask in to_date() function, it will take default format for date datatype. In our case this is 'dd-mn-yyyy'.

As we have converted the date to some other format, the to_date() function will not be able to convert the character string to date. Now using the following statement, we can always get the output in date datatype.

select to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy') from dual;

The difference is that at this time we are passing that the character passed has the format mask dd/mm/yyyy and convert it to date.