Oracle Date Functions Usage and Examples

All the Oracle date functions list is the following:
add_months
current_date
current_timestamp
dbtimezone
extract(datetime)
last_day
months_between
new_time
next_day
round
sysdate
systimestamp
to_date
trunc()

Oracle date function 1.Add_Months

The syntax for the add_months function is:
    Add_Months(date,months)    Get a date added or minused the months.
Example:
          select add_months(date,1) from dual
          date
          ------
          16-Oct-11
          select add_months(date,-1) from dual
           date
           ------
          16-Aug-11

Oracle date function 2.Current_date

The syntax for the add_months function is:
         Current_date    Return the date of the current session time zone.
Example:
        select current_date,sysdate from dual  (default current system time zone)
        SQL> select current_date,sysdate from dual ;
        CURRENT_DATE SYSDATE
        --------------- ---------------
20111119 142754 20111119 142753
        ALTER SESSION SET TIME_ZONE = '-3:0';
        SQL> select current_date,sysdate from dual;
        CURRENT_DATE SYSDATE
         --------------- ---------------
20111119 142754 20111119 142753

Oracle date function 3.Current_timestamp

 The syntax for the Current_timestamp function is:
       Current_timestamp(precision)    Return the date and time of the current session time zone.

Example:
 A:default timestamp
       SQL> select CURRENT_TIMESTAMP from dual;
       CURRENT_TIMESTAMP
       ------
       19-NOV-11 13.27.34.514674 PM +08:00
B: use precision
      SQL> ALTER SESSION SET TIME_ZONE ='-5:0';
      Session altered.
      SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL
      SESSIONTIMEZONE CURRENT_TIMESTAMP
      ------
      -05:00 19-NOV-11 13.26.56.484566 AM -05:00
C: You can user others date funtions on current_timestamp

     SQL> select to_char(current_timestamp,'YYYYMMDD') from dual;

     to_char
     ------
     20111119

Oracle date function 4.Dbtimezone

 The syntax for the dbtimezone function is:
     dbtimezone    Return the database server's timezone.
Example:
     SQL>select dbtimezone from dual;
DBTIME
------
+00:00

Oracle date function 5.Exreact

 The syntax for the Exreact function is:
     Exreact(year from date )    Extract and return the date and time.
Example:
A:
     Select Exreact(year from sysdate ) from dual
     Select Exreact(year from to_date('20111119','YYYYMMDD')) from dual
     Exreact(year from to_date('20111119','YYYYMMDD'))
-----------------------------------------------
2011
B:
     SQL> Select Exreact(month from to_date('20111119','YYYYMMDD')) from dual;
     Exreact(month from to_date('20111119','YYYYMMDD'))
------------------------------------------------
11

Oracle date function 6.Last_day

The syntax for the  Last_day function is:
     last_day(date)   Return the specified date month last day.
Example:
     SQL> SELECT SYSDATE,LAST_DAY(SYSDATE) "Last",LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL;
     SYSDATE      Last                 Days Left
---------          ---------            ----------
19-NOV-11   30-NOV-11  11

Oracle date function 7.Months_between

The syntax for the  Months_between function is:
     Months_between(date1,date2) ---(date1-date2)     Return the number of months between two dates.(Oracle default 31 days per month )
Example:
     select months_between(sysdate-3,sysdate) "num"
     months_between(sysdate-3,sysdate)*31 "num1" from dual
     num                    num1
     ----------             ----------
-.09677419    -3

Oracle date function 8.New_time

The syntax for the  New_time function is:
     New_time( date , zone1 , zone2 )    Return to the date and time in a time zone  1 corresponding to the date and time in time zone 2.
Zone1  and Zone2 paremeters list:
1 AST, ADT: Atlantic Standard or Daylight Time
2 BST, BDT: Bering Standard or Daylight Time
3 CST, CDT: Central Standard or Daylight Time
4 EST, EDT: Eastern Standard or Daylight Time
5 GMT: Greenwich Mean Time
6 HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.
7 MST, MDT: Mountain Standard or Daylight Time
8 NST: Newfoundland Standard Time
9 PST, PDT: Pacific Standard or Daylight Time
10 YST, YDT: Yukon Standard or Daylight Time
Example:
     SQL> SELECT New_time(sysdate,'AST','PST') "New Date and Time",sysdate FROM DUAL;
     New Date and Time               SYSDATE
-------------------                       -------------------
2011-11-20 07:36:25         2011-11-20 11:36:25

Oracle date function 9.Next_day

The syntax for the  Next_day function is:
     Next_day( date , char )    Return the date starting from the date the next Char corresponding to the day of week.
Example:
A: Next monday
     SELECT next_day(SYSDATE,'Mon") FROM dual
     NEXT_DAY(SYSDATE,'Mon")
--------------------------
11-11-19 11:35:23
B:If your NLS_DATE_LANGUAGE  is Spanish
      NEXT_DAY ('01-JAN-2011', 'LUNES') ==> 06-JAN-2011

Oracle date function 10.Round

The syntax for the  Round function is:
     Round (date IN DATE [, format_mask VARCHAR2])    Return date corresponding to format Format_mask date.
If  format_mask is omitted then return the last day date.

Oracle date function 11.To_date

The syntax for the  Round function is:
      To_date( char, fmt, ’ nlsparam ’)   Convert Char, varchar, nchar or nvarchar2 data type string  to a date type.
Fmt expresss a specific date format type.
Example:
     SELECT TO_DATE(’January 15, 2011, 11:00 A.M.’,’Month dd, YYYY, HH:MI A.M.’,’NLS_DATE_LANGUAGE = American’)
FROM DUAL;

Oracle date function 12.Trunc

The syntax for the  Trunc function is:
     Trunc ( date,fmt)    Return amputated a portion of the date in a specific format (fmt).
Example:
First day of this month.
select trunc(sysdate,'month') from dual
First day of this year.
     select trunc(sysdate,'year') from dual
Last day of this month.
select last_day(sysdate) from dual
First day of this week.
select trunc(sysdate,'day') from dual --Oracle default start week is Sunday
select trunc(sysdate,'iw') from dual ---ISO year default start week is Monday
Monday of this week.
select trunc(sysdate,'day')+1 from dual --2,3,4,5,6,
     select trunc(sysdate,'iw')+1 from dual --2,3,4,5,6,
Oracle Date Functions Usage and Examples.


This address is http://www.computerites.com/database-knowledge/2012/11/oracle-date-functions-usage-and-examples-572.html