DATE

DATE

(All limits valid for 11gR2 and 12c)

The DATE type is used both in SQL and PL/SQL. It can store dates (including time up to seconds) from 1 January 4712 BC to 31 December 9999 AD.

The DATE type has a fixed size of 7 bytes, which consists of CENTURY, YEAR, MONTH, DAY, HOUR, MINUTE and SECOND. A DATE does not have a fraction for seconds or a time zone. To use higher accuracy or time zones, use a TIMESTAMP (WITH TIMEZONE).

Let’s look at the border values and the storage of the DATE:

To convert a string value to a DATE, we need to use the TO_DATE function. (Or the ANSI DATE) I’ll discuss the conversion functions in another article. But see http://www.stijf.com/wordpress/2017/08/datatypes-and-ora-1722/ about the reason to use TO_DATE.

In this case we tested the lower limit of the DATE type. An interesting thing about the ORA-1841 is the last part ‘and not be 0’. So Oracle doesn’t know the year 0?

Let’s say it’s complicated …………. 😉


SQL> insert into ot_dt values (to_date('01-JAN-0000','dd-mon-yyyy'));

insert into ot_dt values (to_date('01-JAN-0000','dd-mon-yyyy'))

*

ERROR at line 1:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0



SQL> insert into ot_dt values (to_date('31-dec-1 BC','dd-mon-yyyy BC'));

1 row created.



SQL> select to_char(dummy,'dd-mon-yyyy BC hh24:mi:ss') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYYYBCHH24

--------------------------------

31-dec-0001 BC 00:00:00



SQL> select to_char(dummy+1,'dd-mon-yyyy BC hh24:mi:ss') from ot_dt;

TO_CHAR(DUMMY+1,’DD-MON-YYYYBCHH

---------------------------------------------------------

01-01-0000 00 00:00:00



SQL> select to_char(dummy+2,'dd-mon-yyyy BC hh24:mi:ss') from ot_dt;

TO_CHAR(DUMMY+2,’DD-MON-YYYYBCHH

---------------------------------------------------------

01-01-0000 00 00:00:00



SQL> select to_char(dummy + INTERVAL '1' DAY,'dd-mon-yyyy BC') from ot_dt;

TO_CHAR(DUMMY+INTERVAL'

-----------------------

00-000-0000 00



SQL> select to_char(add_months(dummy,1),'dd-mon-yyyy BC') from ot_dt;

TO_CHAR(ADD_MONTHS(DUMM

-----------------------

00-000-0000 00

 

And yes, you can also store it in the table again:


SQL> insert into ot_dt select dummy+1 from ot_dt;

1 row created.



SQL> select to_char(dummy,'dd-mon-yyyy BC hh24:mi:ss') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYYYBCHH24

--------------------------------

31-dec-0001 BC 00:00:00

00-000-0000 00 00:00:00

 

Date arithmetic has the ability to return the date 01-01-0000 and 00-00-0000 (based on INTERVAL).

Note that I specifically asked for a format with ‘MON’. But a numeric month is returned.

Oracle does not allow the year 0. But since the internal date operations are based on JULIAN dates. The version of the JULIAN calendar that Oracle uses has a 366 day gap in the year 0, which breaks the calculations in that time frame.

Storage

Back to the Oracle DATE type. As mentioned, it is stored internally in 7 bytes (fixed length). Consider:

SQL> insert into ot_dt values (to_date('3-apr-2017 AD 12:34:56','dd-mon-yyyy BC

HH24:MI:SS'));

1 row created.



SQL> select dump(dummy) from ot_dt;

DUMP(DUMMY)

--------------------------------------------------------------------------------

Typ=12 Len=7: 120,117,4,3,13,35,57



SQL> insert into ot_dt values (to_date('3-apr-2017 BC 12:34:56','dd-mon-yyyy BC

HH24:MI:SS'));

1 row created.


SQL> select dump(dummy) from ot_dt;

DUMP(DUMMY)

--------------------------------------------------------------------------------

Typ=12 Len=7: 120,117,4,3,13,35,57

Typ=12 Len=7: 80,83,4,3,13,35,57

 

The DATE is stored as CENTURY, YEAR, MONTH, DATE, HOUR (24HR), MINUTE and SECOND.

The century and year AD have an offset of 100. This allows the BC dates to be stored with values < 100.

Be aware that hour, minute and second also include an offset of 1.

The HOUR is always stored in the 24HH format:

SQL> insert into ot_dt values (to_date('3-apr-2017 05:34:56PM','dd-mon-yyyy HH:M

I:SSAM'));

1 row created.



SQL> select dump(dummy) from ot_dt;

DUMP(DUMMY)

--------------------------------------------------------------------------------

Typ=12 Len=7: 120,117,4,3,18,35,57

 

Leave a Reply

Your email address will not be published. Required fields are marked *