1) Oracle Date Calculation
1@@@@extract and to_char function in plsql block
@@@Example: use the extract function to handle the DATA INTERVAL data type.
SYS@ocm> !cat tmp.sql
DECLARE
--If your table have start_date and end_date, you could calculate
--how many day your work using extract function. It is also functional in
--SQL statement.
date1 VARCHAR2(32767);
BEGIN
date1 := EXTRACT( DAY FROM ( sysdate - to_date('2003-01-04','yyyy-mm-dd') ) DAY TO SECOND )
||' days ' ||
EXTRACT( HOUR FROM ( sysdate - to_date('2003-01-04','yyyy-mm-dd') ) DAY TO SECOND )
|| ' hours '||
EXTRACT( MINUTE FROM ( sysdate - to_date('2003-01-04','yyyy-mm-dd') ) DAY TO SECOND )
|| ' minutes '||
EXTRACT( SECOND FROM ( sysdate - to_date('2003-01-04','yyyy-mm-dd') ) DAY TO SECOND )
|| ' seconds ';
DBMS_OUTPUT.put_line(date1);
END;
/
SYS@ocm> @tmp.sql
3650 days 12 hours 21 minutes 47 seconds
PL/SQL procedure successfully completed.
@@@Use to_char handle the date data type.
HR@ocm> !cat tmp.sql
DECLARE
cur_var SYS_REFCURSOR;
rec employees%ROWTYPE;
l_counter NUMBER := 0;
BEGIN
OPEN cur_var FOR
SELECT * FROM employees;
LOOP
FETCH cur_var INTO rec;
EXIT WHEN cur_var%NOTFOUND;
IF to_char(rec.hire_date,'YYYY')
BETWEEN 2000 AND 2003
THEN
l_counter := l_counter + 1;
END IF;
END LOOP;
DBMS_OUTPUT.put_line('In 2000-2003, company hire '||l_counter||' employees.');
CLOSE cur_var;
END;
/
HR@ocm> @tmp.sql
In 2000-2003, company hire 11 employees.
PL/SQL procedure successfully completed.
2@@@@ extract day information in sql statement
@@@
@@@<1> use built-in function to_char to extract the date information, both sql and pl/sql
@@@
SYS@ocm> !cat tmp.sql
SELECT sysdate FROM dual
/
SELECT to_char(sysdate,'YYYY') years
, to_char(sysdate,'MM') month
, to_char(sysdate,'DD') day
, to_char(sysdate,'HH24') hour
, to_char(sysdate,'MI') minutes
, to_char(sysdate,'SS') seconds
FROM dual
/
SYS@ocm> @tmp.sql
SYSDATE
-------------------
2013-01-04 12:47:07
YEAR MO DA HO MI SE
---- -- -- -- -- --
2013 01 04 12 47 07
@@@
@@@<2> use built-in function extract date and date interval information
@@@ both sql/plsql.
@@@extract function for DATE INTERVAL data type
SYS@ocm> !cat tmp.sql
SELECT
extract( YEAR FROM
(sysdate - to_date('2003-01-04','yyyy-mm-dd') )
YEAR TO MONTH ) years
, extract( MONTH FROM
(sysdate - to_date('2003-01-04','yyyy-mm-dd') )
YEAR TO MONTH ) months
FROM dual
/
SELECT
extract( DAY FROM
(sysdate - to_date('2003-01-04','yyyy-mm-dd') )
DAY TO SECOND ) days
, extract( HOUR FROM
(sysdate - to_date('2003-01-04','yyyy-mm-dd') )
DAY TO SECOND ) hours
, extract( MINUTE FROM
(sysdate - to_date('2003-01-04','yyyy-mm-dd') )
DAY TO SECOND ) minutes
, extract( SECOND FROM
(sysdate - to_date('2003-01-04','yyyy-mm-dd') )
DAY TO SECOND ) seconds
FROM dual
/
SYS@ocm> @tmp.sql
YEARS MONTHS
---------- ----------
10 0
DAYS HOURS MINUTES SECONDS
---------- ---------- ---------- ----------
3653 12 56 6
@@@extract function for DATE data type,
Note: here could not display hour, minute and second.
SYS@ocm> !cat tmp.sql
SELECT
extract( YEAR FROM (sysdate) ) year
, extract( MONTH FROM (sysdate) ) month
, extract( DAY FROM (sysdate) ) day
FROM dual
/
SYS@ocm> @tmp.sql
YEAR MONTH DAY
---------- ---------- ----------
2013 1 4
转载于:https://blog.51cto.com/majesty/1107580