TIME type in Oracle (newbie)

Hi, I'm a newbie to Oracle DB. I'm wondering, how to store time in an Oracle DB. I found only DATETIME type, that stores date and time together in only one field. But how to store the time only, if I want to search by this field. Is there any way, how to set the date part of the DATETIME or is there any usuall well-known solution to this issue?

Thanx
MooMoo

Hi,

if you want to insert time then use this:


insert into tablename values(to_char(sysdate,'hh:mi:ss'))
/

Hi,

what I need is to insert the time provided by myself and to search the table using time only. If I use DATETIME to store the time, there's also a date part, that is also compared. But I need to search only by the time part of the DATETIME.
Did you mean, that I should store the time as a CHAR field???

MooMoo

No, don't use char datatype for timestamp, TIMESTAMP or DATE datatype can be used, but to match the timestamp only, you need to specify timestamp part, an example is given below:
SELECT empno, ename, to_char(hiredate,'DD-MON-YYYY HH24:MI:SS') FROM emp
WHERE to_char(hiredate,'HH24:MI:SS') between to_char(to_date(hiredate,'HH24:MI:SS),'HH24:MI:SS') AND to_char(to_date('16:00:00','HH24:MI:SS'),'HH24:MI:SS')

Note: Be careful updating timestamp part of any DATE column because the date part is automatically set to the current system date if no date is provided by the user.
/

A number that counts the number of seconds since midnight, or milliseconds if you require better precision. Often easier to work with than constantly needing to use to_char and to_date.