working with dates
•
oracle stores dates
in numeric format internally.
•
The dates in range
from January 1,4712 bc to December
31,9999 ad.
•
The default display
and input format is DD-MON-YY.
The following are
some of date functions.
1.sysdate,
2.add_months(),
3.months_between(),
4.next_day(),
5.last_day(),
6.round(),
7.truncate().
1.sysdate:-used to display current system date and time.
It is
generally performed on dual table.
EX.SQL>
select sysdate from dual;
SYSDATE
---------
18-SEP-13
Date arithmetic:-dates are stored in number format so oracle allow to
perform arithmetic operations on dates.
Date+number: adds a number of days to the date
EX.SQL>
select sysdate,sysdate+10 from dual;
SYSDATE SYSDATE+1
---------
---------
18-SEP-13
28-SEP-13
Date-number: subtracts a number of days from the date
EX.SQL>
select sysdate,sysdate-10 from dual;
SYSDATE SYSDATE-1
---------
---------
18-SEP-13
08-SEP-13
Date-date :subtracts from one day to another. Give the result in
days.
EX1.SQL>
select sysdate-(sysdate-10) from dual;
SYSDATE-(SYSDATE-10)
--------------------
10
EX2.SQL>
select ename,hiredate,sysdate-hiredate from emp;
ENAME HIREDATE
SYSDATE-HIREDATE
----------
--------- ----------------
SMITH 17-DEC-80 11963.6236
ALLEN 20-FEB-81 11898.6236
WARD 22-FEB-81 11896.6236
JONES 02-APR-81 11857.6236
MARTIN 28-SEP-81 11678.6236
BLAKE 01-MAY-81 11828.6236
CLARK 09-JUN-81 11789.6236
SCOTT 09-DEC-82 11241.6236
KING 17-NOV-81 11628.6236
TURNER 08-SEP-81 11698.6236
ADAMS 12-JAN-83 11207.6236
JAMES 03-DEC-81 11612.6236
FORD 03-DEC-81 11612.6236
MILLER 23-JAN-82 11561.6236
14
rows selected.
2.add_months():-adds n number of calendar months to the date.
Syntax:add-months(date,n);//n
is number of months ,n is +ve or –ve.
EX1.SQL>
select add_months(sysdate,3) from dual;
ADD_MONTH
---------
18-DEC-13
EX2.SQL>
select add_months(sysdate,0) from dual;
ADD_MONTH
---------
18-SEP-13
EX3.SQL>
select add_months(sysdate,-3) from dual;
ADD_MONTH
---------
18-JUN-13
3.months_between():-Gives the difference between two dates in months.
Syntax:-months_between(date1,date2);
EX1.SQL>
select months_between('31-aug-13','31-aug-12') noofmonths from dual;
NOOFMONTHS
----------
12
EX2.SQL>
select ename,hiredate,months_between(sysdate,hiredate) from emp
where months_between(sysdate,hiredate)<320;
no
rows selected
EX3.SQL>
select ename,hiredate,months_between(sysdate,hiredate) from emp where
months_between(sysdate,hiredate)>320;
ENAME HIREDATE
MONTHS_BETWEEN(SYSDATE,HIREDATE)
----------
--------- --------------------------------
SMITH 17-DEC-80 393.051484
ALLEN 20-FEB-81 390.95471
WARD 22-FEB-81 390.890194
JONES 02-APR-81 389.535355
MARTIN 28-SEP-81 383.696645
BLAKE 01-MAY-81 388.567613
CLARK 09-JUN-81 387.309549
SCOTT 09-DEC-82 369.309549
KING 17-NOV-81 382.051484
TURNER 08-SEP-81 384.341807
ADAMS 12-JAN-83 368.212774
JAMES 03-DEC-81 381.503097
FORD 03-DEC-81 381.503097
MILLER 23-JAN-82 379.857936
14
rows selected.
4.next_day():-Gives the next day specified
by char that is later than the date.
Syntax:-next_day(date,char);//char
must be in the specified format of date .
EX1.SQL>
select next_day(sysdate,'mon') from dual;
NEXT_DAY(
---------
23-SEP-13
EX2.SQL>
select next_day(sysdate,'thu') from dual;
NEXT_DAY(
---------
19-SEP-13
EX3.SQL>
select next_day(sysdate) from dual;
select
next_day(sysdate) from dual
*
ERROR
at line 1:
ORA-00909:
invalid number of arguments
EX4.SQL>
select ename,hiredate,next_day(hiredate,'monday') from emp;
ENAME HIREDATE
NEXT_DAY(
----------
--------- ---------
SMITH 17-DEC-80
22-DEC-80
ALLEN 20-FEB-81 23-FEB-81
WARD 22-FEB-81 23-FEB-81
JONES 02-APR-81
06-APR-81
MARTIN 28-SEP-81 05-OCT-81
BLAKE 01-MAY-81
04-MAY-81
CLARK 09-JUN-81
15-JUN-81
SCOTT 09-DEC-82
13-DEC-82
KING 17-NOV-81 23-NOV-81
TURNER 08-SEP-81
14-SEP-81
ADAMS 12-JAN-83
17-JAN-83
JAMES 03-DEC-81
07-DEC-81
FORD 03-DEC-81 07-DEC-81
MILLER 23-JAN-82
25-JAN-82
14
rows selected.
SQL>
select ename,hiredate,next_day(hiredate,'monday') from emp where
next_day(hiredate,'monday')='22-dec-80';
ENAME HIREDATE
NEXT_DAY(
----------
--------- ---------
SMITH 17-DEC-80 22-DEC-80
5.last_day():-Gives the date of last day of the month.
Syntax:-last_day(date);
EX1.SQL>
select last_day(sysdate) from dual;
LAST_DAY(
---------
30-SEP-13
EX2.SQL>
select last_day(sysdate)-sysdate from dual;
LAST_DAY(SYSDATE)-SYSDATE
-------------------------
12
EX3.SQL>
select last_day(sysdate)-sysdate daysleft
from dual;
DAYSLEFT
----------
12
6.round():-Gives the date rounded to the unit specified by the formt.
Syntax:-round(date,
’format’);//if format is omitted then date is
round to the nearest date.
EX1.SQL>
select round(sysdate,'day') from dual;
ROUND(SYS
---------
22-SEP-13
EX2.SQL>
select round(sysdate,'year') from dual;
ROUND(SYS
---------
01-JAN-14
EX3.SQL>
select round(sysdate,'month') from dual;
ROUND(SYS
---------
01-OCT-13
EX4.SQL>
select round(sysdate,'mon') from dual;
ROUND(SYS
---------
01-OCT-13
7.trunc():-Gives the date truncated to the unit specified by the format.
Syntax:-trunc(date,
’format’);// if format is omitted then date is
truncated to the nearest date.
EX1.SQL>
select trunc(sysdate,'mon') from dual;
TRUNC(SYS
---------
01-SEP-13
EX2.SQL>
select trunc(sysdate,'year') from dual;
TRUNC(SYS
---------
01-JAN-13
EX3.SQL>
select trunc(sysdate,'day') from dual;
TRUNC(SYS
---------
15-SEP-13
EX4.SQL>
select round(sysdate,'day'),trunc(sysdate,'day') from dual;
ROUND(SYS
TRUNC(SYS
---------
---------
22-SEP-13
15-SEP-13
EX5.SQL>
select round(sysdate,'mon'),trunc(sysdate,'mon') from dual;
ROUND(SYS
TRUNC(SYS
---------
---------
01-OCT-13
01-SEP-13
EX6.SQL>
select round(sysdate,'year'),trunc(sysdate,'year') from dual;
ROUND(SYS
TRUNC(SYS
---------
---------
01-JAN-14
01-JAN-13
EX7.SQL>
select ename,hiredate,sysdate-hiredate diff,round(sysdate-hiredate)
rounded,trunc(sysdate-hiredate) truncated from emp;
ENAME HIREDATE DIFF
ROUNDED TRUNCATED
----------
--------- ---------- ---------- ----------
SMITH 17-DEC-80 11963.6259 11964
11963
ALLEN 20-FEB-81 11898.6259 11899
11898
WARD 22-FEB-81 11896.6259 11897
11896
JONES 02-APR-81 11857.6259 11858
11857
MARTIN 28-SEP-81 11678.6259 11679
11678
BLAKE 01-MAY-81 11828.6259 11829
11828
CLARK 09-JUN-81 11789.6259 11790
11789
SCOTT 09-DEC-82 11241.6259 11242
11241
KING 17-NOV-81 11628.6259 11629
11628
TURNER 08-SEP-81 11698.6259 11699
11698
ADAMS 12-JAN-83 11207.6259 11208
11207
JAMES 03-DEC-81 11612.6259 11613
11612
FORD 03-DEC-81 11612.6259 11613
11612
MILLER 23-JAN-82 11561.6259 11562
11561
14
rows selected.
No comments:
Post a Comment