DATE : DATE « PL SQL Data Types « Oracle PL/SQL Tutorial






The DATE datatype is used to store date and time values.

The time component is always there whether you use it or not.

The range for date variables is from 1 Jan 4712 BC through 31 Dec 4712 AD.

If you do not specify a time when assigning a value to a variable of type DATE, it will default to midnight (12:00:00 a.m.).

The Syntax for the DATE Datatype

variable_name DATE;

Here are some examples:

hire_date DATE;
emp_birthdate DATE;

Use the following code to declare the DATE datatype:

SQL> declare
  2    variable1_dt DATE;
  3  begin
  4    NULL;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Be careful when comparing dates.

If you really don't care about the time of day, you can use the TRUNC() function.

For example, instead of

IF hire_date = fire_date THEN...

use

IF TRUNC(hire_date) = TRUNC(fire_date) THEN...








21.6.DATE
21.6.1.DATE
21.6.2.Define DATE type variable
21.6.3.Date literals
21.6.4.Displaying Date and Time
21.6.5.Using the DATE Format Mask
21.6.6.Using Format Masks mixed with user string
21.6.7.Assignment value to DATE type variable during declaration
21.6.8.Use SYSDATE to initialize an DATE type variable
21.6.9.Define DATE type variable, assign value TRUNC(SYSDATE)
21.6.10.Use FOR LOOP to loop through dates
21.6.11.Compare DATE type variable in IF statement
21.6.12.Compare DATE type variable after truncating
21.6.13.Combine TO_NUMBER and TO_DATE to get different parts of a Date
21.6.14.Performing Calculations on a Converted Date
21.6.15.Converting DATE to a Spelled-Out Character Format
21.6.16.Calculate the months between two dates
21.6.17.Use the date value back and forth
21.6.18.Remember That Dates Are Numbers
21.6.19.Check the last date that a date type variable can hold
21.6.20.Use the new EXTRACT function to grab individual year and month components.
21.6.21.ROUND function being applied to datetime values
21.6.22.TO_CHAR(C,'YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD')