Please tell me how we can define the data type for the date field to store only time, not date, like hh24:mm.
I would say that I would still use the DATE datatype. But I would just set the day to be the same day. For instance, if I had to store "12:12", "13:13", and "09:59" into a table, I would probably use INSERT statements similar to the following:
INSERT INTO my_table VALUES TO_DATE('01/01/01 '||'12:12','MM/DD/YY HH24:MI'); INSERT INTO my_table VALUES TO_DATE('01/01/01 '||'13:13','MM/DD/YY HH24:MI'); INSERT INTO my_table VALUES TO_DATE('01/01/01 '||'09:59','MM/DD/YY HH24:MI');Why would I do this? Because with this method, date arithmetic operations still work. You can compute the difference between two different values with just a subtraction operator, for instance. If date arithmetic is not important to you, then you may consider just storing these values in a character field.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.