MassBot1: Created page with "=Oracle Dates and Times ----= * Overview * DATE Format * yu..."

2013-11-05T05:35:39Z

Created page with "=Oracle Dates and Times ----= * Overview * DATE Format * yu..."

New page

=Oracle Dates and Times ----=

* [[yurttas/PL/DBL/oracle/F/R/#overview|Overview]]
* [[yurttas/PL/DBL/oracle/F/R/#date format|DATE Format]]
* [[yurttas/PL/DBL/oracle/F/R/#the current time|The Current Time]]
* [[yurttas/PL/DBL/oracle/F/R/#operations on date|Operations on DATE]]
* [[yurttas/PL/DBL/oracle/F/R/#further information|Further Information]]

===---- Overview===

Oracle does support both date and time, albeit differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, <tt>DATE</tt>. The <tt>DATE</tt> 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 <tt>DATE</tt> type is used in the same way as other built-in types such as <tt>INT</tt>. For example, the following SQL statement creates a relation with an attribute of type <tt>DATE</tt>:

create table x(a int, b date);

===---- <tt>DATE</tt> Format===

When a <tt>DATE</tt> 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 <tt>TO_CHAR</tt>, according to a <tt>DATE</tt> ''format''. Oracle's default format for <tt>DATE</tt> is "<tt>DD-MON-YY</tt>". Therefore, when you issue the query

select b from x;

you will see something like:

<nowiki>B
---------
01-APR-98</nowiki>

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

select to_char(b, 'YYYY/MM/DD') as b
from x;

returns the result:

<nowiki>B
---------------------------------------------------------------------------
1998/04/01</nowiki>

The general usage of <tt>TO_CHAR</tt> is:

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

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

{| width="100%" border="BORDER"
| <tt>MM</tt>
| Numeric month (''e.g.'', <tt>07</tt>)
|-
| <tt>MON</tt>
| Abbreviated month name (''e.g.'', <tt>JUL</tt>)
|-
| <tt>MONTH</tt>
| Full month name (''e.g.'', <tt>JULY</tt>)
|-
| <tt>DD</tt>
| Day of month (''e.g.'', <tt>24</tt>)
|-
| <tt>DY</tt>
| Abbreviated name of day (''e.g.'', <tt>FRI</tt>)
|-
| <tt>YYYY</tt>
| 4-digit year (''e.g.'', <tt>1998</tt>)
|-
| <tt>YY</tt>
| Last 2 digits of the year (''e.g.'', <tt>98</tt>)
|-
| <tt>RR</tt>
| Like <tt>YY</tt>, but <tt>06</tt> is considered <tt>2006</tt> instead of <tt>1906</tt>
|-
| <tt>AM</tt> (or <tt>PM</tt>)
| Meridian indicator
|-
| <tt>HH</tt>
| Hour of day (<tt>1</tt>-<tt>12</tt>)
|-
| <tt>HH24</tt>
| Hour of day (<tt>0</tt>-<tt>23</tt>)
|-
| <tt>MI</tt>
| Minute (<tt>0</tt>-<tt>59</tt>)
|-
| <tt>SS</tt>
| Second (<tt>0</tt>-<tt>59</tt>)
|}

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

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

Alternatively, you may use <tt>TO_DATE</tt> explicitly:

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

The general usage of <tt>TO_DATE</tt> is:

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

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

Finally, you can change the default <tt>DATE</tt> format of Oracle from "<tt>DD-MON-YY</tt>" to something you like by issuing the following command in <tt>sqlplus</tt>:

alter session set NLS_DATE_FORMAT='<my_format>';

The change is only valid for the current <tt>sqlplus</tt> session.

===---- The Current Time===

The built-in function <tt>SYSDATE</tt> returns a <tt>DATE</tt> value 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;

returns

<nowiki>Current Time
---------------------------------------------------------------------------
Tue 21-Apr-1998 21:18:27</nowiki>

which is the time when I was preparing this document <tt>:-)</tt> 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 <tt>"Current Time"</tt> is not quoted, it would have been interpreted as ''two'' case insensitive names <tt>CURRENT</tt> and <tt>TIME</tt>, which would actually cause a syntax error.
* <tt>DUAL</tt> is built-in relation in Oracle which serves as a dummy relation to put in the <tt>FROM</tt> clause when nothing else is appropriate. For example, try "<tt>select 1+2 from dual;</tt>".

===---- Operations on <tt>DATE</tt>===

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

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

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

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

===---- Further Information===

Inside <tt>sqlplus</tt>, type

help date;

for more information. 

----

This document is written originally by Kristian Widjaja for Prof. Jeff Ullman's CS145 class in Autumn, 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998.

Yurttas/PL/DBL/oracle/F/R/or-time.html - Revision history

MassBot1: Created page with "=Oracle Dates and Times ----= * Overview * DATE Format * yu..."