CODEWITHSUNDEEP
mysqloracle-databasejira
rym
rym

Reputation: 545

Problem with Date field after migration from mysql to oracle

I have lately migrate my JIRA database from mysql to oracle, my problem is the field "created" exists in the jiraissue and changegroup tables,on this field I effectuate many calculation but I was surprised by the difference of the format of the fields.

in mysql database the field creation has the type timeStamp so it has the follwing format:

enter image description here

and in Oracle database it has the type date and the format like the following:

enter image description here

How can I resolve this problem?

Upvotes: 1

Views: 849

Answers (2)

Ollie
Ollie

Reputation: 17538

The format of your displayed Oracle DATE column is due to your IDE that you are viewing it through.

Oracle stores all portions of a date, to display the full date stored use this:

SELECT TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS')
  FROM jiraissue;

This will show you the full date that has been stored including the time portion. To store timestamps you need the column to be designated as a timestamp datatype column.

Ollie.

EDIT: You could change the NLS Date Format of your IDE to always show the full date format in it's settings somewhere.

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132580

Dates are not held as formatted text in Oracle. What you are seeing is a tool's (Toad's?) default formatting of the date to display it to you. This can be changed via a preference somewhere. It could be that the time component has been lost in migration, but that is unlikely. Try running this SQL to see:

select to_char (created, 'YYYY-MM-DD HH24:MI:SS') from jiraissue;

That should show the dates just as they appeared in MySQL.

Upvotes: 1

Related Questions