Why does Oracle store time in DATE column?

Question

I have a table with a date column. I used JDBC to save a java.util.Date object into the table.

When I run following query I get date aswell as a time value:

select To_CHAR(departure_date,'DD-MM-YYYY HH:MI') from service

This tells me that date is column is also storing time.

This is unexpected. Shouldn't date type discard time value like an integer type discards fractional digits?

Philᵀᴹ · Accepted Answer · 2014-03-05 10:05:49Z

7

Oracle's DATE datatype behaves as both an ANSI DATE and TIME datatype. This was a design decision that only Oracle knows the reasoning for.

I'll add that Oracle isn't the only RDBMS that doesn't follow ANSI SQL standards in this way.

Obviously, you can remove the time portion with TO_CHAR(departure_date,'DD-MM-YYYY').

answered Mar 5, 2014 at 10:05

Philᵀᴹ

31.8k10 gold badges83 silver badges107 bronze badges

4

or with TRUNC(departure_date) if you wish to preserve the date-time nature of the value.
– Adam Musch
Mar 7, 2014 at 14:50

Add a comment |

1 Answer 1