PLSQL

PL/SQL Datetime and interval Types

PL/SQL Datetime and interval Types

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.