Oracle PL/SQL Tutorial :LAST_DAY, TO_CHAR, TO_DATE

Oracle PL/SQL Tutorial :LAST_DAY, TO_CHAR, TO_DATE
SQL>

SQL> SELECT TO_CHAR(LAST_DAY(TO_DATE('01APR2011','ddMONyyyy'))

 'Month dd, yyyy') FROM dual;

TO_CHAR(LAST_DAY(T
------------------
  April 01, 2011

SQL>
SQL>

Oracle PL/SQL Date Timestamp Functions :Date 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>

Oracle PL/SQL DateTimestamp Functions :Time Interval Related Functions

FunctionDescription
NUMTODSINTERVAL(x, interval_unit)Converts the number x to an INTERVAL DAY TO SECOND with the interval for x supplied in interval_unit, which you may set to DAY, HOUR, MINUTE, or SECOND.
NUMTOYMINTERVAL(x, interval_unit)Converts the number x to an INTERVAL YEAR TO MONTH with the interval for x supplied in interval_unit, which you may set to YEAR or MONTH.
TO_DSINTERVAL(x)Converts the string x to an INTERVAL DAY TO SECOND.
TO_YMINTERVAL(x)Converts the string x to an INTERVAL YEAR TO MONTH.

Oracle PL/SQL Date Timestamp Functions :Timestamp-Related Functions

FunctionDescription
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.

Oracle PL/SQL Date Timestamp Functions :Using Datetime Functions

In the following table, x represents a datetime or a timestamp.
FunctionDescription
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_TIMEReturns 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.