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 |