you're reading...
Database, IT-Center, Oracle

Oracle Date and Time

A. Getting Year, Month, Day from Date

SQL> -- EXTRACT(): return a year, month, day, hour, minute, second, or time zone from x;


B. Date Format & Number Format


Convert a numeric or date expression to a character String.

      to_char(expression [,'format'] [,nls_format])

   char      The DATE, NUMBER or expression to convert
   format    Format to use.
   nls_lang  The international language to use.

The format may be either a DATE format (YYYY=year, MM=month, DD=Day, HH=Hour, Mi=Minute )
or a NUMBER format (0999=include leading zero).

If no format is specified Oracle will use the default date format.
nls_format allows international formats to be applied.

TO_CHAR will convert NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set.


SQL> Select to_char(sysdate, 'yyyy/mm/dd') FROM dual;

SQL> Select to_char(sysdate, 'FMMonth DD, YYYY') FROM dual;
 'June 9, 2005'

SQL> select to_char(sysdate,'HH24:MI:SS') "Time Now" from dual;

SQL> Select to_char(1.234, '9999.9') FROM dual;

SQL> Select to_char(1000.25, '9,999.99') FROM dual;

SQL> Select to_char(1000.25, '$9,999.00') FROM dual;

SQL> Select to_char(25, '000099') FROM dual;

SQL> Select to_char(-50, 'PR999') FROM dual;

SQL> Select to_char(17, 'RN99') FROM dual;

SQL> Select to_char('01110' + 1) FROM dual;

SQL> Select to_char(timestamp, 'DD-MM-YYYY HH24:MI') FROM dual;
 31-12-2005 23.30

Convert a character string into an Oracle date, then convert back to a string with a different date format:

SQL> Select to_char(mydate,'DD-MON-RR HH12:MI') Short_Date_Time
 from (
   select to_date('1-MAR-2010 23:24','DD-MON-RRRR HH24:MI') mydate
   from dual



Oracle supports both date and time, albeit differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also
the hour, minute, and second.

The DATE type is used in the same way as other built-in types such as INT. For example, the following SQL statement creates a relation with an attribute of type DATE:

create table x(a int, b date);

DATE Format

When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle’s default format for DATE is “DD-MON-YY“. Therefore, when you issue the query

select b from x;

you will see something like:


Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATEformat. For example,


returns the result:


The general usage of TO_CHARis:

TO_CHAR(<date>, '<format>')

where the <format> string can be formed from over 40 options. Some of the more popular ones include:
, for example.

MM Numeric month (e.g., 07)
MON Abbreviated month name (e.g., JUL)
MONTH Full month name (e.g., JULY)
DD Day of month (e.g., 24)
DY Abbreviated name of day (e.g., FRI)
YYYY 4-digit year (e.g., 1998)
YY Last 2 digits of the year (e.g., 98)
RR Like YY, but the two digits are “rounded” to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM) Meridian indicator
HH Hour of day (112)
HH24 Hour of day (023)
MI Minute (059)
SS Second (059)

You have just learned how to output a DATE value using TO_CHAR. Now what about inputting a DATE value? This is done through a function called TO_DATE, which converts a string to a DATE value, again according to the DATE format. Normally, you do not have to call TO_DATE explicitly: Whenever Oracle expects a DATE value, it will automatically convert your input string using TO_DATE according to the default DATE format “DD-MON-YY“. For example, to insert a tuple with a DATE attribute, you can simply type:

insert into x values(99, '31-may-98');

Alternatively, you may use TO_DATEexplicitly:

insert into x
values(99, to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));

The general usage of TO_DATEis:

TO_DATE(<string>, '<format>')

where the <format> string has the same options as in TO_CHAR.

Finally, you can change the default DATE format of Oracle from “DD-MON-YY” to something you like by issuing the following command in sqlplus:

alter session set NLS_DATE_FORMAT='<my_format>';

The change is only valid for the current sqlplussession.

The Current Time

The built-in function SYSDATE returns a DATEvalue containing the current date and time on your system. For example,

select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
from dual;


Current Time
Tue 21-Apr-1998 21:18:27

which is the time when I was preparing this document 🙂Two interesting things to note here:

  • You can use double quotes to make names case sensitive (by default, SQL is case insensitive), or to force spaces into names. Oracle will treat everything inside the double quotes literally as a single name. In this example, if "Current Time" is not quoted, it would have been interpreted as two case insensitive names CURRENT and TIME, which would actually cause a syntax error.
  • DUAL is built-in relation in Oracle which serves as a dummy relation to put in the FROM clause when nothing else is appropriate. For example, try “select 1+2 from dual;“.

Another name for the built-in function SYSDATE is CURRENT_DATE. Be aware of these special names to avoid name conflicts.

Operations on DATE

You can compare DATE values using the standard comparison operators such as =, !=, >, etc.

You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. In general, the result may contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two DATE values are not allowed.

You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. For example, SYSDATE+1 will be tomorrow. You cannot multiply or divide DATE values.

With the help of TO_CHAR, string operations can be used on DATE values as well. For example, to_char(<date>, 'DD-MON-YY') like '%JUN%' evaluates to true if <date> is in June.

See Also : http://psoug.org/reference/date_func.html

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
« Nov   Jan »


Web Statistic

Blog Stats

  • 149,551 hits
%d bloggers like this: