Developers Arena: Oracle PL/SQL Date Timestamp Functions

We must format dates to see all of the information contained in a date column.

We use the TO_CHAR function to do the converting.

 SQL> SELECT SYSDATE FROM dual;



SYSDATE

---------

01-APR-11



SQL>

 SQL> SELECT TO_CHAR(SYSDATE, 'ddMONyyyy') Today

  2  FROM dual;



TODAY

---------

01APR2011



SQL>

Function	Description
CURRENT_TIMESTAMP()	Returns a TIMESTAMP WITH TIME ZONE containing the current session time along with the session time zone.
EXTRACT({ YEAR \| MONTH \| DAY \| HOUR \| MINUTE \| SECOND } \| { TIMEZONE_HOUR \| TIMEZONE_MINUTE } \| { TIMEZONE_REGION \| } TIMEZONE_ABBR ) FROM x)	Extracts and returns a year, month, day, hour, minute, second, or time zone from x; x may be one of the timestamp types or a DATE.
FROM_TZ(x, time_zone)	Converts the TIMESTAMP x and time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE.
LOCALTIMESTAMP()	Returns a TIMESTAMP containing the current time in the session time zone.
SYSTIMESTAMP()	Returns a TIMESTAMP WITH TIME ZONE containing the current database time along with the database time zone.
SYS_EXTRACT_UTC(x)	Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC.
TO_TIMESTAMP(x, [format])	Converts the string x to a TIMESTAMP. You may also specify an optional format for x.
TO_TIMESTAMP_TZ(x, [format])	Converts the string x to a TIMESTAMP WITH TIMEZONE. You may also specify an optional format for x.

In the following table, x represents a datetime or a timestamp.

Function	Description
ADD_MONTHS(x, y)	Add y months to x. If y is negative, y months are subtracted from x.
LAST_DAY(x)	Get the last day of the month.
MONTHS_BETWEEN(x, y)	Returns the number of months between x and y. If x appears before y on the calendar, the number returned is positive.
NEXT_DAY(x, day)	Returns the datetime of the next day following x; day is specified as a literal string, for example SATURDAY.
NEW_TIME	Returns the time/day value from a time zone specified by the user.
ROUND(x [, unit])	Rounds x. By default, x is rounded to the beginning of the nearest day. You may supply an optional unit string to indicate the rounding unit.
SYSDATE()	Returns the current datetime set for the operating system.
TRUNC(x [, unit])	Truncates x. By default, x is truncated to the beginning of the day. You may supply an optional unit string that indicates the truncating unit.

Developers Arena