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 NameValid Datetime ValuesValid Interval Values
YEAR-4712 to 9999 (excluding year 0)Any nonzero integer
MONTH01 to 120 to 11
DAY01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale)Any nonzero integer
HOUR00 to 230 to 23
MINUTE00 to 590 to 59
SECOND00 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_MINUTE00 to 59Not applicable for DATE or TIMESTAMP.Not applicable
TIMEZONE_REGIONNot applicable for DATE or TIMESTAMP.Not applicable
TIMEZONE_ABBRNot 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.

Leave a Reply

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