# Databases > Oracle Date Function in Oracle

## Robert

How to compare dates in Oracle? An insight about the date functions in Oracle in this discussion would help us know about that.

----------


## jamesravid

You can use any comparison operators(such as =,<,>,<>) to compare two dates.

--James.

----------


## Robert

Fine. But is there any predefined date functions available in Oracle. Can you give me an idea on those?

----------


## jamesravid

Actually there are many more date functions available in oracle. do refer oracle standard documentations to learn them


-- James

----------


## raghav_sy

Hi, there are lot of date functions available. That much of time is not there to explain everything, so i am pasting here some material, which i prepared when i entered to this software field, may be it will help u:

select sysdate + 3 from dual;
 ---------------------------------------------------- 
select '07-dec-04' + 3 from dual; -- gives error
 select to_date('07-dec-04') + 3 from dual;
 select to_date('07-dec-04') - to_date('03-dec-04') from dual;
 select to_date('07-dec-04') - to_date('13-dec-04') from dual; -- negative value 
--------------------------------------------------------------- 
date + number/24 --> adds a number of hours to a date suppose the time of sysdate is 9:30 pm and 5 hours are added in it then it will be2:30 am of the next date. So it shows the next date.
 Select sysdate + 4/24 from dual; 
---------------------------------------------------
 months_between(d1,d2) if d1 > d2 then +ve else -ve 
select months_between(sysdate,hiredate) from emp select round(months_between(sysdate,hiredate),0) as "months between" from emp 

assignment - find the years between using months_between 

select months_between('13-dec-04','24-jul-04') from dual
 select round(months_between('13-dec-04','24-jul-04'),0) from dual 
add_months(date,n) [n cand be +ve or -ve] 
select add_months('4-dec-04',3) from dual; gives 04-mar-05 
select add_months('4-dec-04',-3) from dual; gives 04-sep-04 
-----------------------------------------------------------------------------------------------
 next_day(date,'char') -- give the date of the next week for the specified day in the char parameter. 
Select next_day('04-dec-04','wed') from dual;
 gives the date coming on next wednesday after 4-dec-04. The answer is 08-dec-04 or instead of character parameter a numeric value can be also given. It has sunday as 1 monday as 2 ...saturday as 7 
select next_day('04-dec-04', 4) from dual. The answer is 08-dec-04 
note: the number cannot be negative.
 ---------------------------------------------------------------------- 
last_day(date) - returns the last date of the month. Select last_day(sysdate) from dual; 
--------------------------------------------------------------------------
 round(date,fmt) if month is the fmt, then rounds the date to the months extremities.if date is between 1 and 15 then it rounds the date to the first of the same month. If it is 16 to end then rounds to first date of the next date. E.g select round(to_date('04-dec-04'),'month') from dual ----> 01-dec-04 
select round(to_date('15-dec-04'),'month') from dual; ----> 01-dec-04 
select round(to_date('16-dec-04'),'month') from dual; ----> 01-jan-05 
select round(to_date('02-feb-04'), 'year') from dual; --> 01-jan-04 
select round(to_date('30-jun-04'), 'year') from dual; --> 01-jan-04 
select round(to_date('1-jul-04'), 'year') from dual; --> 01-jan-05 
------------------------------------------------------------------ 
trunc select trunc(to_date('23-dec-04'),'month') from dual; gives 01-dec-04 
select trunc(to_date('07-dec-04'),'month') from dual; gives 01-dec-04 
select trunc(to_date('23-dec-04'),'year') from dual; gives 01-jan-04 
select trunc(to_date('02-feb-04'),'year') from dual; gives 01-jan-04
 ------------------------------------------------------------------------ 
to_char to see all the records of employees joined after 1982
 select * from emp where to_char(hiredate,'yy') >= 82; or where to_char(hiredate,'yyyy’) >= 1982 

to see all the records of employees who have joined from october of any month. 
Select * from emp where to_char(hiredate,'mm') >= 10;
 (note  only number 1 to 12 can be used for comparison) to see all the records of employees who have joined from 21st date. 
Select * from emp where to_char(hiredate,'dd') >= 21; 
to see all the records of employees who have joined from april-81 
select * from emp where to_char(hiredate,'mm') > 04 and to_char(hiredate,'yy') >= 81
 to see all the records of employees who have joined after 28-sep-81 
select * from emp where hiredate > '28-sep-81'
 ---------------------------------------------------------------------
 date format model years 
select to_char(hiredate,'yyyy') || '-' || to_char(hiredate,'year') from emp; 
months 
select to_char(hiredate,'month') || '-' || to_char(hiredate,'mm') || '-' || to_char(hiredate,'mon') from emp;
 days
 select to_char(hiredate,'day') || '-' || to_char(hiredate,'dd') || '-' || to_char(hiredate,'dy') from emp;
 advanced formats – century 
select to_char(sysdate,’scc’) from dual; 
select to_char(hiredate,'scc') || '-' || to_char(hiredate,'yy') "cen-yr" from emp; 
syear the spelled out year. 
Select to_char(hiredate,'syear') "year" from emp
 q to get the quarter of the year (1,2,3 and 4) 
select to_char(hiredate,'q') "quarter" from emp; 
rm roman numeral month
 select to_char(hiredate,'rm')"month" from emp;
 j julian day – the number of days since 31dec 4713 b.c. 
Select to_char(hiredate,'j')"julianday" from emp;
 ----------------------------------------------------------------------- 
time select to_char(sysdate,'hh:mi:ss') from dual;
 to show am or pm
 select to_char(sysdate,'hh:mi:ss')|| '-' || to_char(sysdate,'pm') from dual;
 ssss seconds past midnight (0-86399) [ (60secs * 60 min * 24) – 1]
 select to_char(sysdate,'ssss') from dual; suffixes – select to_char(sysdate,'ddth') from dual; 
select to_char(sysdate,'ddsp') from dual; select to_char(sysdate,'ddspth') from dual; 
---------------------------------------------------------------- 

regards,
RSY

----------

