Problem solve Get help with specific problems with your technologies, process and projects.

Handling time data formats in conversion from DB2

I'm porting an application from DB2/MVS to Oracle 8i. My application has a time data type and column in DB2 that is populated by the application. How can I handle this in Oracle, since it has no time data type?

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:

Day        Time
---------- --------
01/01/2002 12:00:00
01/02/2002 01:23:45
12/31/1999 01:01:01
You 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

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.