Date

DATE is a data type used to store date and time values in a 7-byte structure.

Oracle can handle date ranges from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or "AD"). Unless BCE ("BC" in the format mask) is specifically used, CE date entries are the default.

Examples

SQL> CREATE TABLE t1(c1 DATE);
Table created.

SQL> INSERT INTO t1 VALUES (SYSDATE);
1 row created.

SQL> SELECT * FROM t1;
C1
---------
10-DEC-07

SQL> SELECT TO_CHAR(c1, 'DD/MON/YYYY HH24:MI:SS') FROM t1;
TO_CHAR(C1,'DD/MON/Y
--------------------
10/DEC/2007 10:31:01

Internal storage

The date type takes 7 bytes of storage:

SQL> SELECT dump(c1) FROM t1;
DUMP(C1)
------------------------------------
Typ=12 Len=7: 120,107,12,19,23,36,11

The internal format is the following one:

1. century + 100
2. year in the century + 100
3. month
4. day
5. hour + 1
6. minute + 1
7. second + 1

So in the previous example the date was 19-DEC-2007 at 22:35:10.

Also see

• Timestamp

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#