Dates

  Oracle stores dates in an internal numeric format. The dates in Oracle range from Jan 1, 4712 BC to Dec 31, 9999 AD.
   The default display and input format for any date is DD-MMM-YY.

1.SYSDATE
   It returns the current date and time set for the operating system on which the database resides.

SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
11-JAN-16

2.SYSTIMESTAMP
   It  returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
11-JAN-16 08.49.24.798699 PM +05:30

3.SYS_EXTRACT_UTC
  It extracts the UTC from a datetime value with time zone offset or time zone region name.

SQL> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00')
 FROM DUAL; 2
SYS_EXTRACT_UTC(TIMESTAMP'2000-03-2811:30:00.00-08:00')
---------------------------------------------------------------------------
28-MAR-00 07.30.00.000000000 PM

4.SESSIONTIMEZONE
  It returns the time zone of the current session. The return type is a time zone offset or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement.

SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00

5.CURRENT_DATE
  It returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.

SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE
---------------------------------------------------------------------------
CURRENT_D
---------
-05:00
11-JAN-16

6.CURRENT_TIMESTAMP
  It returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. 

SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE
---------------------------------------------------------------------------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
-05:00
11-JAN-16 10.26.56.189162 AM -05:00

 
7.ADD_MONTHS
  It returns the date date plus integer months. The date argument can be a datetime value or any value that can be implicitly converted to DATE.

SQL> SELECT TO_CHAR(ADD_MONTHS(CURRENT_DATE,1)) FROM DUAL;
TO_CHAR(A
---------
11-FEB-16

8.LAST_DAY
  It returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype
of date.

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
LAST_DAY(
---------
31-JAN-16
SQL>

9.DBTIMEZONE
  It returns the value of the database time zone. The return type is a time zone offset or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIME
------
+00:00

10.FROM_TZ
  It converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value.

SQL> SELECT FROM_TZ(TIMESTAMP '2016-01-11 13:00:00', '3:00') FROM DUAL;
FROM_TZ(TIMESTAMP'2016-01-1113:00:00','3:00')
---------------------------------------------------------------------------
11-JAN-16 01.00.00.000000000 PM +03:00

11.NEW_TIME
   It returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.

SQL>ALTER SESSION SET NLS_DATE_FORMAT ='DD-MON-YYYY HH24:MI:SS';
SQL> SELECT NEW_TIME(TO_DATE('01-10-16 22:06:00', 'MM-DD-YY HH24:MI:SS'),'AST', 'PST') "New Date and Time" FROM DUAL;
New Date and Time
--------------------
10-JAN-2016 18:06:00

12.NEXT_DAY
   It returns the date of the first weekday named by char that is later than the date date.

SQL> SELECT NEXT_DAY('10-JAN-2016','SUNDAY') "NEXT DAY" FROM DUAL;
NEXT DAY
--------------------
17-JAN-2016 00:00:00

13.MONTHS_BETWEEN
  It returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative.

SQL> SELECT MONTHS_BETWEEN (TO_DATE('02-02-2016','MM-DD-YYYY'),TO_DATE('01-01-2015','MM-DD-YYYY') ) "Months" FROM DUAL;
Months
----------
13.0322581

14.EXTRACT (datetime)
  It extracts and returns the value of a specified datetime field from a datetime or interval value expression.

SQL> SELECT EXTRACT(YEAR FROM DATE '2015-09-01') FROM DUAL;
EXTRACT(YEARFROMDATE'2015-09-01')
---------------------------------
 2015

15.NUMTODSINTERVAL
   It converts n to an INTERVAL DAY TO SECOND literal.

16.NUMTOYMINTERVAL
   It converts number n to an INTERVAL YEAR TO MONTH literal.

17.ROUND (date)
   It returns date rounded to the unit specified by the format model.

SQL> SELECT ROUND (TO_DATE ('08-NOV-15'),'YEAR') "New Year" FROM DUAL;
New Year
--------------------
01-JAN-0016 00:00:00

18.TO_CHAR (datetime)
  It converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format.

19.TO_TIMESTAMP
   It converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

SQL> SELECT TO_TIMESTAMP ('10-JAN-16 14:13:10.143000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;
TO_TIMESTAMP('10-JAN-1614:13:10.143000','DD-MON-RRHH24:MI:SS.FF')
---------------------------------------------------------------------------
10-JAN-16 02.13.10.143000000 PM

20.TO_TIMESTAMP_TZ
   It converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype.

SQL> SELECT TO_TIMESTAMP_TZ('2016-12-01 03:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
TO_TIMESTAMP_TZ('2016-12-0103:00:00-8:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
---------------------------------------------------------------------------
01-DEC-16 03.00.00.000000000 AM -08:00
SQL>

21.TO_DSINTERVAL
 It converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND value.

22.TO_YMINTERVAL
 It converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted.

23.TRUNC (date)
  It returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date.

SQL> SELECT TRUNC(TO_DATE('17-OCT-16','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL;
New Year
--------------------
01-JAN-2016 00:00:00

24.TZ_OFFSET
  It returns the time zone offset corresponding to the argument based on the date the statement is executed.

SQL> SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
TZ_OFFS
-------
-05:00

25.LOCALTIMESTAMP

  It returns the current date and time in the session time zone in a value of datatype TIMESTAMP.

SQL> ALTER SESSION SET TIME_ZONE = '-5:00';
Session altered.
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
11-JAN-16 10.21.10.183070 AM -05:00
11-JAN-16 10.21.10.183070 AM

Advertisements