PL/SQL: Date & Time Cheat Sheet

Two classes of date and time related data types in PL/SQL

• Datetime data types
• Interval data types

The Datetime data types

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

The Interval data types are

• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

Field Values for Datetime and Interval Data Types

Field Name	Valid Datetime Values	Valid Interval Values
YEAR	-4712 to 9999 (excluding year 0)	Any nonzero integer
MONTH	01 to 12	0 to 11
DAY	01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale)	Any nonzero integer
HOUR	00 to 23	0 to 23
MINUTE	00 to 59	0 to 59
SECOND	00 to 59.9(n), where 9(n) is the precision of time fractional secondsThe 9(n) portion is not applicable for DATE.	0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
TIMEZONE_HOUR	-12 to 14 (range accommodates daylight savings time changes)Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_MINUTE	00 to 59Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_REGION	Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_ABBR	Not applicable for DATE or TIMESTAMP.	Not applicable

The Datetime Data Types and Functions

• Datetime functions

ADD_MONTHS(x, y);	Adds y months to x.
LAST_DAY(x);	Returns the last day of the month.
MONTHS_BETWEEN(x, y);	Returns the number of months between x and y.
NEXT_DAY(x, day);	Returns the datetime of the next day after x.
NEW_TIME;	Returns the time/day value from a time zone specified by the user.
ROUND(x [, unit]);	Rounds x.
SYSDATE();	Returns the current datetime.
TRUNC(x [, unit]);	Truncates x.

• Timestamp functions

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.
FROM_TZ(x, time_zone);	Converts the TIMESTAMP x and the time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE.
LOCALTIMESTAMP();	Returns a TIMESTAMP containing the local 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.
TO_TIMESTAMP_TZ(x, [format]);	Converts the string x to a TIMESTAMP WITH TIMEZONE.

The Interval Data Types and Functions

NUMTODSINTERVAL(x, interval_unit);	Converts the number x to an INTERVAL DAY TO SECOND.
NUMTOYMINTERVAL(x, interval_unit);	Converts the number x to an INTERVAL YEAR TO 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.