How to distinguish date at midnight from date w/o time in Oracle?

Question

I would like to distinguish date at midnight (i.e. '12/05/2010 00:00:00') from date without time specified (i.e. '12/05/2010').
This value is provided by user, sometimes it is only date which is important, sometimes it is date and time and later the application is expected to show the time only when it was explicitly provided. So for the following inputs I would expect the following outputs:

• for '12/05/2010' I would expect '12/05/2010'
• for '12/05/2010 09:23' I would expect '12/05/2010 09:23'
• for '12/05/2010 00:00' I would expect '12/05/2010 00:00'

I know I can model it in two separate columns, date and time, but I was wondering was there any way of handling this in single date column?

Answer 1

It is possible to indicate that a date was supplied without the time component by negating the year to store that fact.

  User supplied value       Inserted value

  12/05/2010                12/05/2010 BC
  12/05/2010 09:23          12/05/2010 09:23
  2/05/2010 00:00           2/05/2010 00:00

When selecting the date value if the year is a BC year then the user omitted the time component.

Having said that, my actual advice is to use a dedicated column to save a flag regarding the presence of the time component or not. I would not use this negation technique myself.