Results 1 to 5 of 5

Thread: Date Function in Oracle

  1. #1
    Contributing Member
    Join Date
    Jul 2006
    Answers
    50

    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.


  2. #2
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Date Function in Oracle

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

    --James.


  3. #3
    Contributing Member
    Join Date
    Jul 2006
    Answers
    50

    Re: Date Function in Oracle

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


  4. #4
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Date Function in Oracle

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


    -- James


  5. #5
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    Re: Date Function in Oracle

    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

    Last edited by raghav_sy; 02-16-2007 at 06:31 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact