oracle date var,Oracle Date Calculation

最新推荐文章于 2022-03-25 00:46:16 发布

Rainfall若

最新推荐文章于 2022-03-25 00:46:16 发布

阅读量119

点赞数

文章标签： oracle date var

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