Hi I have created a table which has a location and time columns

CREATE TABLE TEST
(location CHAR(20) PRIMARY KEY,
Time date );
INSERT INTO TEST VALUES
(1,TO_DATE('14:30:00','HH24:MI:SS'));

When i

select * from test;

all i get from the time column is 1-Nov-07

How do i insert the time in date field and then retrieve the entered time with a query

You're doing that.

It's stored as a date data type, which is a binary representation of a date. When you select * it, the server returns the default collation for date values. You would want to apply formatting to the value coming out of the query to see it in one format versus another.

Although on reflection it looks like you're using MySQL instead of SQL Server, and a time data type. Is that set up to store both the date and time?