List Of Oracle Time/Date Function

Oracle provides a data type date that behaves like a timestamp. It stores a date and a time. Internally a date is stored in a special format not visible to the user.

To see the system date and time use the following functions :

  • CURRENT_DATE :returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE
    • select  current_date from dual;
    • 11-SEP-10
  • SYSDATE :Returns the current date and time.
    • select  sysdate from dual;
    • 12-SEP-10
  • SYSTIMESTAMP :The SYSTIMESTAMP function returns the system date, including fractional seconds and time zone of the database. The return type is TIMESTAMP WITH TIME ZONE.
    • select  systimestamp from dual;
    • 12-SEP-10 02.17.11.664000 AM +05:30

A user can store and retrieve dates and times through the following main functions:

  • to_date( str, format )

str is a sting of characters and format indicates how the string is supposed to be interpreted. The format components are listed in the following table. The function returns the internal representation of a date specifies by the string in the given format.

  • to_char( date, format )

date is the internal representation of an Oracle date and format is as before. The function returns a character string representing the given date in the given format.

FORMAT DESCRIPTION EXAMPLE
D Day – number of day in the week 5
DD Day – number of day within the month 26
DDD Day – number of day in the year 235
DY Day – three letter SUN
DAY Day – full name SUNDAY
MM Month-number 10
MON Month-three letters FEB
MONTH Month-full name FEBRYARY
Y Year – last digit 3
YY Year – last two digits 03
YYY Year – last three digits 003
YYYY Year – four digits 2003
HH12 Hour in 1-12 format 11
HH24 Hour in 0 -24 format 19
MI Minutes 54
SS Seconds 45
AM Displays AM or PM (depending on the time) PM


Oracle has a number of functions that apply to a date
Oracle date functions


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.
    • SELECT SESSIONTIMEZONE FROM DUAL;
    • +05:30
NLS_DATE_FORMAT
  • As the name suggests, this determines the DATE format
    • SELECT VALUE
    • FROM nls_session_parameters
    • WHERE parameter = ‘NLS_DATE_FORMAT’;
  • To change the format, use the following ALTER query
    • 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.
    • SELECT TO_DATE (’24-OCT-2010′) 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
    • SELECT TO_CHAR (SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) FROM DUAL;
    • 24-MAY-2009 10:28:43
+/-
  • 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.
    • 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.
    • 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.
    • SELECT TO_CHAR (SYSDATE + INTERVAL ’10’ HOUR, ‘HH:MI:SS’) 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.
    • SELECT SYSDATE FROM DUAL;
    • 5/24/2009 10:54:49 PM
    • SELECT TRUNC (SYSDATE) FROM DUAL;
    • 5/24/2009

NLS_DATE_FORMAT

  • As the name suggests, this determines the DATE format
    • SELECT VALUE
    • FROM nls_session_parameters
    • WHERE parameter = ‘NLS_DATE_FORMAT’;
    • To change the format, use the following ALTER query
    • ALTER SESSION SET nls_date_format = ‘DD-MON-YYYY HH24:MI:SS’;

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.
    • 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.
    • SELECT TO_CHAR (SYSDATE + INTERVAL ’10’ HOUR, ‘HH:MI:SS’) 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.
    • SELECT SYSDATE FROM DUAL;
    • 5/24/2009 10:54:49 PM
    • SELECT TRUNC (SYSDATE) FROM DUAL;
    • 5/24/2009

MONTHS_BETWEEN

  • To see how many months have passed since 15-aug-1947, use the MONTHS_BETWEEN function.
    • SELECT MONTHS_BETWEEN(’01-JAN-10′,’01-AUG-01′) FROM Dual;
    • 101

LAST_DAY

  • To see the last date of the month of a given date, Use LAST_DAY function.
    • select LAST_DAY(sysdate) from dual;
    • 31-Sep-2010

NEXT_DAY

  • To see when the next Saturday is coming, use the NEXT_DAY function.
    • SELECT NEXT_DAY(SYSDATE, ‘SAT’) FROM dual;
    • 18-SEP-2010

EXTRACT (Oracle 9i, 10g only)

  • An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation
  • The syntax of EXTRACT function is
    • EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE FROM DATE)
    • Select extract(year from sysdate) from dual;
    • 2003
Share

About the Author

Akash Padhiyar

Visit Website

3 Comments

  1. Hi there, just doing some research for my Stuhrling website. Can’t believe the amount of information out there. Wasn’t what I was looking for, but good site. Have a nice day.

  2. Author

    Thank you so Much Dena ..also Check this Site Shareittips.com/

Trackbacks for this post

  1. […] List Of Oracle Time/Date Function | Ty Bca Oracle Notes | BCA HUB […]

Leave a Comment

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.