We came across a need to save just the Time in the database, the requirement is to store time of the day, like say the user likes to have Breakfast at 8.15AM and Lunch at 12.32PM etc. Off course oracle does not have a time only data type. So we ended up using DATE as the data type and just setting the time. for example:
CREATE TABLE FOO (PREFERRED_TIME DATE NULL);
INSERT INTO FOO (TO_DATE('11:34','HH24:MI'));
oracle automatically sets the date to the first day of the current month. so when you do a select from the FOO table the data would be
SELECT TO_CHAR(PREFERRED_TIME, 'dd-mon-yyyy hh24:mi:ss') from FOO; ----------------------- 01-feb-2009 11:24:00
on the client side you will have to know to ignore the date component, this is possible area for confusion in future, since I could have many preferences over different months and my preferred time would have different date components, ideally I would just want the time, otherwise I would think having a time component and the date being a constant known value like 01/01/0001. We could achieve this using a BEFORE INSERT/UPDATE trigger which keeps the time component but updates the date component to a known constant value or you can also use your OR mapping layer like hibernate to set the value as such. If you are using hibernate, you can map the filed using the java.sql.Time object and the date is automatically set to 01/01/1970. SqlServer 2008 seems has a Time data type.
