Oracle Data Types – Why Oracle Stores Time in DATE Column

datatypesoracle

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?

Best Answer

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

Related Solutions

Sql-server – Best pratice to store dates group by months-year key par value

I'd use a date field with the 1st of a month and a CHECK constraint to ensure it stays day 1.

This keeps it in the native date/time format (which is your observation about option 3)

Option 1 would requires less storage, but complicates comparisons. And 5 million rows isn't much: you use less storage but add code and query complexity

Simple query with date type requires explicit conversion to_date of date

Your belief that this should not be necessary is correct based on your assumptions. Normally this means the assumptions should be rechecked (unless there is data corruption or a bug). If you run the following you should get the same results from the first query as the second:

DROP TABLE t1;
CREATE TABLE t1 AS (
   SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn 
   FROM dual CONNECT BY level <=120);

select * from t1 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

select myDateColumn from t1 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

To check some of the assumptions, can you show the results of the following query?

SELECT myDateColumn, to_date(myDateColumn)
   , to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY') 
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

The first three columns should all show identical information and the last should verify that the year is correct.

Best Answer

Related Solutions

Sql-server – Best pratice to store dates group by months-year key par value

Simple query with date type requires explicit conversion to_date of date

Related Question