Home » RDBMS Server » Server Administration » Time fields
Time fields [message #370570] Fri, 24 December 1999 14:39 Go to next message
K.krishnamoorthy
Messages: 3
Registered: December 1999
Junior Member
How can I insert only time value (10:30AM) into table having date column
Re: Time fields [message #370571 is a reply to message #370570] Sat, 25 December 1999 09:04 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Krishnamoorthy,
You can't - Oracle dates store both the date and the time. On the other hand, you can select / display the stored information so that only the hour and minute are visible in the output
as in:
SELECT TO_CHAR(my_date,'HH:MI A.M.')
FROM my_table;

You can compare the value of one date against the value of another based solely on the hours and minutes as in:

SELECT my_column, TO_CHAR(my_date,'HH:MI A.M.')
FROM my_table
WHERE (my_date - TRUNC(my_date)) > (my_other_date - TRUNC(my_other_date));

date_col - TRUNC(date_col) yields the time since midnight as a decimal fraction.

Your other alternative, is to add a NUMBER(10,4) column to your table and store the time element in seconds in this column which you can populate through a row level before insert database trigger that would basically subtract TRUNC(SYSTDATE) from SYSDATE, multiply the result by 84600, then use the result to populate the new column. You would then need a to create a function to convert the value of this column to time format.
Hope this helps,
Paul
Re: Time fields [message #370582 is a reply to message #370571] Tue, 28 December 1999 17:25 Go to previous message
Edward Jayaraj
Messages: 7
Registered: December 1999
Junior Member
You can store time value while inserting records
in ORACLE Table: For eg:

(For a table with only one column of type Date)

insert into table_name
values
( to_date(
'01/01/1999:01:10:20','mm/dd/yyyy:hh:mi:ss'
)
)
Previous Topic: QUERY/SQL/PLSQL
Next Topic: dbms_output.put_line
Goto Forum:
  


Current Time: Thu Mar 28 23:41:09 CDT 2024