Oracle handles time and dates in the same datatype, the DATE datatype. Internally, the DATE datatype is represented as a floating point number where the integer portion of the number represents the "day" and the decimal portion of the number represents the "time" of that day. You'll be better off if you use the DATE datatype to hold all of your dates and times.
So how do you populate this field correctly? Let's assume that I have a table such as the following:
Table_A: Day Time ---------- -------- 01/01/2002 12:00:00 01/02/2002 01:23:45 12/31/1999 01:01:01You can probably get this data from your table with the following query:
SELECT day, time FROM table_a;How do you convert this to Oracle's DATE datatype? You do this with the TO_DATE function. This can be done similar to the following command:
INSERT INTO new_table SELECT TO_DATE(day||' '||time,'MM/DD/YYYY HH24:MI:SS') FROM table_a;The TO_DATE function takes two parameters. The first is a character string representation of the date and time. The second parameter is the format of that string.
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.
Dig Deeper on Oracle data and application integration
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.