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')