PLSQL

PL/SQL DATE and TIME

PL/SQL DATE and TIME

In PL/SQL there are two different kinds of classes of date and time related data types in PL/SQL. 

1.Datetime data types

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

2.Interval data types 

  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND

For both datetime and interval data types, have fields. These field values determine the value of the data type. Below is the table of the lists of fields.

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(acc. to the rules of the calendar)

Any nonzero integer.

HOUR

00 to 23

0 to 23

MINUTE

00 to 59

0 to 59

SECOND

00 to 59.9(n) precision time of time fractional seconds.

The 9(n) portion is not applicable for DATE.

00 to 59.9(n) precision time of interval fractional seconds.

TIMEZONE_HOUR

-12 to 14 

Not applicable for DATE or TIMESTAMP

Not Applicable

TIMEZONE_MINUTE

Not 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