The DATE datatype is used for storing fixed length data times, that can include the time of day in seconds since midnight. Valid data ranges from January 1, 4712 BC to December 31, 9999 AD. This default format is set by the oracle initialization parameter, NLS_DATA_FORMAT. Like, the default might be ‘DD-MON-YY’, that includes a two-digit number for the day of the month, an abbreviation of the month name and the last two digits of the year. Foe example 01-sep-2021. Each and every date includes the century, year, month, day, hour, minute and second. List of the valid values for each field are given below:
- YEAR
Excluding 0 it can be taken from -4712 to 9999.
- MONTH
Month ranges from the number 01 to 12.
- DAY
Days started from the 01 to 31 limited by the values of month and year, according to the rules of the calendar for the local.
- HOUR
It starts from 00 to 23.
- MINUTE
It starts from 00 to 59.
- SECOND
It can start from 00 to 59.9(n) where 9(n) is the precision of the fractional seconds.
- TIMEZONE_HOUR
This range accommodates daylight savings time can be changed that can be from -12 to 14.
- TIMEZONE_MINUTE
It ranges from 00 to 59.
- TIMEZONE_REGION
This can be found in the dynamic performance view V$TIMEZONE_NAMES
- TIMEZONE_ABBR
This can be found in the dynamic performance view V$TIMEZONE_NAMES.