Re: How to store 'time' values?? Date field?

In article <7o484d$k8a$1_at_nnrp1.deja.com>, <stan_herrman_at_my-deja.com> wrote:
>In article <37a58145.67228329_at_Oracle8>,
> han_at_royal.net wrote:
>> >I will also have to store the standard times at which
>> > different employees
>> >should come in. (i.e. factory staff at 8:00, office staff
>> >at 9:00, computer people at 11:00 ( :-) )
>> >and so on. Then compare the times at which people
>> >come in with these standard times.
>
>Actually, here is what I've found to be the most challenging part of
>using times: How do I just store a time, that has no logical date
>component??? If you know that it will always be on the hour, then I
>guess it could be a number(2) with a check constraint to ensure 0 <=
>hour <= 23.
>
>In the past, I've had a varchar2(4) column, with check constraints to
>ensure the hour (substr(time,1,2)) is proper and the minutes (substr
>(time,3,2)) is proper. However, it must get converted to a numerical
>portion of a day anytime I need to use it in cojunction with a date.
>
>I am certainly interested in any other approaches to this issue, that I
>have missed....

I would use January 01, 0001 as the standard year. This way, nobody (intelligent) would ever mistake the date for an actual date.

Assuming you have a varchar2(4) field, time4, with hours and minutes (24-hour time), then you could do the following:

work_date := to_date('00010101'||time4||'00','YYYYMMDDHH24MISS');

Steve Cosner Received on Mon Aug 02 1999 - 19:26:00 CEST