Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_selecting015.htm

Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_selecting015.htm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<script src="./callback.js" type="text/javascript"></script>
<noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript>
</head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />

<meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" />
<meta name="date" content="2009-04-21T9:46:23Z" />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content="Using Datetime Functions in Queries" />
<meta name="relnum" content="11g Release 2 (11.2)" />
<meta name="partnum" content="E10766-01" />
<link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" />
<link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" />
<link rel="contents" href="toc.htm" title="Contents" type="text/html" />
<link rel="prev" href="tdddg_selecting014.htm" title="Previous" type="text/html" />
<link rel="next" href="tdddg_selecting016.htm" title="Next" type="text/html" />
<title>Using Datetime Functions in Queries</title>
</head>
<body>
<div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div>
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr valign="bottom">
<td align="left"></td>
<td align="center"><a href="tdddg_selecting014.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_selecting016.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
<p><a id="BCGCAGFF" name="BCGCAGFF"></a><a id="TDDDG22540" name="TDDDG22540"></a></p>
<div class="sect2">
<h1>Using Datetime Functions in Queries</h1>
<a name="BEGIN" id="BEGIN"></a>
<p><a id="sthref134" name="sthref134"></a>Datetime functions operate on date, timestamp, and interval values. Each datetime function returns a single value for each row that is evaluated. The datetime functions that SQL supports are listed and described in <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF20033','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p>
<p>To understand the following example, you must understand the <code>JOB_HISTORY</code> table.</p>
<p>When an employee changes jobs, the <code>START_DATE</code> and <code>END_DATE</code> of his or her previous job are recorded in the <code>JOB_HISTORY</code> table. Employees who have changed jobs more than once have multiple rows in the <code>JOB_HISTORY</code> table, as the following query and its results show:</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT * FROM JOB_HISTORY
ORDER BY EMPLOYEE_ID;
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
EMPLOYEE_ID START_DAT END_DATE  JOB_ID     DEPARTMENT_ID
----------- --------- --------- ---------- -------------
        101 21-SEP-89 27-OCT-93 AC_ACCOUNT           110
        101 28-OCT-93 15-MAR-97 AC_MGR               110
        102 13-JAN-93 24-JUL-98 IT_PROG               60
        114 24-MAR-98 31-DEC-99 ST_CLERK              50
        122 01-JAN-99 31-DEC-99 ST_CLERK              50
        176 24-MAR-98 31-DEC-98 SA_REP                80
        176 01-JAN-99 31-DEC-99 SA_MAN                80
        200 17-SEP-87 17-JUN-93 AD_ASST               90
        200 01-JUL-94 31-DEC-98 AC_ACCOUNT            90
        201 17-FEB-96 19-DEC-99 MK_REP                20
 
10 rows selected.
</pre>
<p>The query in the following example uses the <code>MONTHS_BETWEEN</code> function to show how many months each employee held each of his or her previous jobs. For information about the <code>MONTHS_BETWEEN</code> function, see <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF00669','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p>
<div class="example"><a id="CHDJDBAE" name="CHDJDBAE"></a><a id="TDDDG155" name="TDDDG155"></a>
<p class="titleinexample">Displaying the Number of Months Between Dates</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e.EMPLOYEE_ID,
e.LAST_NAME,
TRUNC(<span class="bold">MONTHS_BETWEEN(j.END_DATE, j.START_DATE)</span>) "Months Worked"
FROM EMPLOYEES e, JOB_HISTORY j
WHERE e.EMPLOYEE_ID = j.EMPLOYEE_ID
ORDER BY "Months Worked";
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
EMPLOYEE_ID LAST_NAME                 Months Worked
----------- ------------------------- -------------
        176 Taylor                                9
        122 Kaufling                             11
        176 Taylor                               11
        114 Raphaely                             21
        101 Kochhar                              40
        201 Hartstein                            46
        101 Kochhar                              49
        200 Whalen                               53
        102 De Haan                              66
        200 Whalen                               69
 
10 rows selected.
</pre></div>

<p>The query in the following example uses the <code>EXTRACT</code> and <code>SYSDATE</code> functions to show how many years each employee in department 100 has been employed. For more information about the <code>SYSDATE</code> function, see <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF06124','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a>. For more information about the <code>EXTRACT</code> function, see <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF00639','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p>
<div class="example"><a id="CHDCEICJ" name="CHDCEICJ"></a><a id="TDDDG156" name="TDDDG156"></a>
<p class="titleinexample">Displaying the Number of Years Between Dates</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT LAST_NAME,
<span class="bold">(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE))</span> "Years Employed"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY "Years Employed";
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
LAST_NAME                 Years Employed
------------------------- --------------
Popp                                   9
Urman                                 10
Chen                                  11
Sciarra                               11
Greenberg                             14
Faviet                                14
 
6 rows selected.
</pre></div>

<p>Suppose that an employee receives his or her first check on the last day of the month in which he or she was hired. The query in the following example uses the <code>LAST_DAY</code> function to show the first pay day for each employee in department 100. For information about the <code>LAST_DAY</code> function, see <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF00654','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p>
<div class="example"><a id="CHDEIFCA" name="CHDEIFCA"></a><a id="TDDDG157" name="TDDDG157"></a>
<p class="titleinexample">Displaying the Last Day of a Selected Month</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT LAST_NAME,
HIRE_DATE "Hired",
<span class="bold">LAST_DAY(HIRE_DATE)</span> "Paid"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
LAST_NAME                 Hired     Paid
------------------------- --------- ---------
Faviet                    16-AUG-94 31-AUG-94
Greenberg                 17-AUG-94 31-AUG-94
Chen                      28-SEP-97 30-SEP-97
Sciarra                   30-SEP-97 30-SEP-97
Urman                     07-MAR-98 31-MAR-98
Popp                      07-DEC-99 31-DEC-99
 
6 rows selected.
</pre></div>

<p>Suppose that an employee receives his or her first evaluation six months after being hired. The query in the following example uses the <code>ADD_MONTHS</code> function to show the first evaluation day for each employee in department 100. For information about the <code>ADD_MONTHS</code> function, see <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF00603','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p>
<div class="example"><a id="CHDECADG" name="CHDECADG"></a><a id="TDDDG158" name="TDDDG158"></a>
<p class="titleinexample">Displaying a Date Six Months from a Selected Date</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT LAST_NAME,
HIRE_DATE "Hired",
<span class="bold">ADD_MONTHS(HIRE_DATE, 6)</span> "Evaluated"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
LAST_NAME                 Hired     Evaluated
------------------------- --------- ---------
Faviet                    16-AUG-94 16-FEB-95
Greenberg                 17-AUG-94 17-FEB-95
Chen                      28-SEP-97 28-MAR-98
Sciarra                   30-SEP-97 31-MAR-98
Urman                     07-MAR-98 07-SEP-98
Popp                      07-DEC-99 07-JUN-00
 
6 rows selected.
</pre></div>

<p>The query in the following example uses the <code>SYSTIMESTAMP</code> function to display the current system time and date. <code>SYSTIMESTAMP</code> is similar to <code>SYSDATE</code>, but it returns more information. For information about the <code>SYSTIMESTAMP</code> function, see <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF06125','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p>
<p>The table in the <code>FROM</code> clause of the query, <a id="sthref135" name="sthref135"></a><code>DUAL</code>, is a one-row table that Oracle Database creates automatically along with the data dictionary. Select from <code>DUAL</code> when you want to compute a constant expression with the <code>SELECT</code> statement. Because <code>DUAL</code> has only one row, the constant is returned only once. For more information about selecting from <code>DUAL</code>, see <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF20036','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p>
<div class="example"><a id="CHDGFGAI" name="CHDGFGAI"></a><a id="TDDDG159" name="TDDDG159"></a>
<p class="titleinexample">Displaying System Date and Time</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' ||
ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP) || '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date"
FROM DUAL;
</pre>
<p>Results depend on current <code>SYSTIMESTAMP</code> value, but have this format:</p>
<pre xml:space="preserve" class="oac_no_warn">
System Time and Date
-------------------------------------------------------------------
18:47:33, 6/19/2008
</pre></div>

<div class="helpinfonotealso">
<h2>Related Topics</h2>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF20033','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a></p>
<p><a href="tdddg_selecting010.htm#BCGCCGJF">Using Operators and Functions in Queries</a></p>
</div>
</div>


<div class="footer">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr>
<td align="left"><span class="copyrightlogo">Copyright&nbsp;&copy;&nbsp;1996, 2009,&nbsp;Oracle&nbsp;and/or&nbsp;its&nbsp;affiliates.&nbsp;All&nbsp;rights&nbsp;reserved.</span><br />
<a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td>
<td align="center"><a href="tdddg_selecting014.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_selecting016.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
</div>

</body>
</html>

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de