Date Function in Oracle

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

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

--James.

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

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


-- James

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