Storing Time information i.e. start time, finish time and retreiving

[Guinea]

Hi Everyone,

I have a schedule table that uses varchar2 as datatype for the fields start_time and end_time. I want to put constraints on the end_time field so that the value must be after 9:00 AM (opening time) and before 9:00 PM (closing time). Also a similar constraint for the start_time. I tried to use to_date function to convert the value so the constraint can contain a comparison clause but cannot make it work.

I suspect that it would be easier to store the fields as a date field but haven't been able to figure out how to do so. Any tips for storing such information so I can compare the time in a constraint.

Thanks,

Marc

 

[faheemrao]

Ok Now first of all , I would recomend that you should store time in 24HRS format like this
YYYY-MM-DD HH24:MI:SS

Now comming to the problem ,
The only function I can think of that might help you is
Months_Between

this function return the number of months between two time vriables
But this can differentiate between the two times

like this if the difference in time is 20 sec it would return this
".000231481"

it works like this
MONTHS_BETWEEN(d1, d2)

if d1 is later than d2 then resultis positive.
and if d2 is later d1 then resultis negative.

Now you can do that you can compare the end_Time. with 9.00(9 am) OR 21.00(9 pm)
it would be like this.

Months_between(end_time,2000-12-12 09:00:00)

now if the result is positive its mean that end_time is later 9 am .)

so that might help !


Faheem

 

[carp]

The easiest way to store these as a date field would be to convert them to datatype DATE. You will need to do an ALTER TABLE MODIFY start_time DATE; command. It also requires that there be no non-null values in that column, which may mean emptying the column, modifying it, and then repopulating it.

It would help solve your immediate problem if you would post some sample data (so we know the format) and your code (so maybe we can spot the problem).

As Faheem suggest, your best bet is to use a 24-hour time format. Assuming your character string is in the format YYYY-MM-DD HH:mm:SS, then one approach might be
a check constraint that verifies
to_number(substr(start_time,12,2)) BETWEEN 9 AND 21
.