1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Date Functions

Discussion in 'SQL PL/SQL' started by tyro, May 24, 2009.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Oracle Date Functions

    I wanted to compile a list of all possible DATE functions in oracle. I am listing a few common ones to begin with, and I look forward to other members adding to this list.

    • SESSIONTIMEZONE
    • NLS_DATE_FORMAT
    • TO_DATE
    • TO_CHAR
    • SYSDATE and CURRENT_DATE
    • +/-
    • ADD_MONTHS
    • INTERVAL
    • LAST_DAY
    • MONTHS_BETWEEN
    • TRUNC

    SESSIONTIMEZONE

    These are the oracle variables which determine your current time and it's format. sessontimezone decides your time zone. The code below gives the SESSIONTIMEZONE.
    Code (SQL):

    SELECT SESSIONTIMEZONE
      FROM DUAL;

    SESSIONTIMEZONE
    +05:30
    We can change the zone by a simple ALTER command as below
    Code (SQL):
    ALTER SESSION SET TIME_ZONE = '-2:0'
    NLS_DATE_FORMAT

    As the name suggests, this determines the DATE format. If you are on SQL Plus, you can check the current nls_date_format by
    Code (SQL):
    SHOW PARAMETER NLS_DATE_FORMAT;
    You can also find the format by the following query.
    Code (SQL):

    SELECT VALUE
      FROM nls_session_parameters
     WHERE parameter = 'NLS_DATE_FORMAT';
    To change the format, use the following ALTER query
    Code (SQL):
    ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
    TO_DATE

    TO_DATE converts a date value as a string into the Oracle DATE format.
    Code (SQL):

    SELECT TO_DATE ('24-MAY-2009)
      FROM DUAL
    TO_CHAR

    TO_CHAR isn't exactly a DATE function, but it helps immensely when we want to display our dates in a particular format. We need to give the format in which we want the date output as the second argument to this function
    Code (SQL):

    SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
      FROM DUAL;

    24-MAY-2009 10:28:43
     
    SYSDATE and CURRENT_DATE

    SYSDATE returns current date/time for the server, in the server's local time while CURRENT_DATE returns current date/time for the server, in the client's/connection's local time.
    Code (SQL):

    SELECT CURRENT_DATE
      FROM DUAL

    SELECT SYSDATE
      FROM DUAL
    +/-

    We can perform plus/minus operations on date values whereby we can add or subtract number of days from dates. The following will give you tomorrow's date.
    Code (SQL):

    SELECT SYSDATE+1
      FROM DUAL
    ADD_MONTHS

    ADD_MONTHS takes two arguments, the first being the date value and second the number of months to add to that date value. We can use negative values to subtract months. Some people ask if there is something like ADD_YEAR. Well no there isn't, why not just give the second argument in multiples of 12.

    Code (SQL):
    SELECT ADD_MONTHS (SYSDATE, 2)
      FROM DUAL;
    INTERVAL

    INTERVAL is used to add or subtract a specific interval in SECOND, MINUTE or HOUR to a date value. We can specify an optional date format to display the output in.

    Code (SQL):
    SELECT TO_CHAR (SYSDATE + INTERVAL '10' HOUR, 'HH:MI:SS')
      FROM DUAL;
     
    You can change the HOUR to MINUTE or SECOND.

    LAST_DAY

    This gives the LAST Day of the month.

    MONTHS_BETWEEN

    This gives the months between two date values.
    Code (SQL):

    SELECT MONTHS_BETWEEN (SYSDATE + 31, SYSDATE)
      FROM DUAL;
     
    TRUNC

    TRUNC is a very useful function and it truncates a date value to give only the date, month and year and not the minutes and seconds.

    Code (SQL):

    SELECT SYSDATE
      FROM DUAL;  

    5/24/2009 10:54:49 PM

    SELECT TRUNC (SYSDATE)
      FROM DUAL;

    5/24/2009
     
    The above list is by no means exclusive. Please feel free to add to the list and give examples.
     
  2. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Thanks a lot.That was very useful for a beginner like me.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    397
    Likes Received:
    47
    Trophy Points:
    405
    Location:
    Khammam
    What is Sysdate and Current_Date ??
    I didnt get you. cCan you explain it clearly ?
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,890
    Likes Received:
    248
    Trophy Points:
    1,155
    I think we can just run the code and see the output.
     
  5. teru1982

    teru1982 Guest

    Thanks a lot.it was very useful