Can anyone tell me if there is a data type used in oracle for recording the time of an event. The SQL books I have read mention the TIME data type but this is not recognised in oracle.
I have tried the timestamp but I require the time and date in separate columns?

At least in my opinion, storing the time and date in different columns is an extraordinarily bad idea. They are both parts of the same thing (a temporal data type), and storing them as two columns makes as much sense as storing money as dollars in one int and the cents in another int... It certainly can be done, but it is a fundamentally bad idea.

If you insist on doing things the hard way, Oracle will gladly let you. Create two separate timestamp columns. Store the date in one of the columns, and the time in the other. Or store both of them in one column, and use the time manipulation functions to extract just the time or date when you only need one of them.

-PatP

what is the time of an event?

do you mean "this event lasts for two hours and fifteen minutes"?

or do you mean "this event occurs at 3:30 pm every day"?

if the former, use an INTERVAL

if the latter, use DATE (which stores date and time) and ignore the date portion