gotchaMinor
Why does Oracle store time in DATE column?
Viewed 0 times
whycolumntimestoredatedoesoracle
Problem
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:
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?
When I run following query I get date aswell as a time value:
select To_CHAR(departure_date,'DD-MM-YYYY HH:MI') from serviceThis 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?
Solution
Oracle's
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
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').Context
StackExchange Database Administrators Q#60167, answer score: 7
Revisions (0)
No revisions yet.