7 Replies - 1237 Views - Last Post: 31 October 2014 - 04:18 AM

#1 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 592
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

CONVERTING SQL TO ORACLE DATE ISSUE

Posted 30 October 2014 - 06:08 AM

i have to convert very large databases from sql to oracle and oracle is not liking the date styles from sql

my sql tables contain the following date styles:

date
smalldate
datetime
datetime2


i have tried converting them with the migrator tool in oracle sql developer to the following:

date
timestamp


however any table that has dates returns empty in oracle

i tried manually placing in 1 row for a test in the empty table and i get the following error:

Quote

One error saving changes to table "DBO_CDOTE470"."BRIDGE":
Row 1: ORA-01861: literal does not match format string
ORA-06512: at line 1


if i remove the dates in place it will commit -- with the dates it will not

dates i am passing:

2005-05-26 13:02:23.0000000


what it will only accept


26-May-2005


i cant just type in convert blah blah because the db's are over 3gigs

Is This A Good Question/Topic? 0
  • +

Replies To: CONVERTING SQL TO ORACLE DATE ISSUE

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: CONVERTING SQL TO ORACLE DATE ISSUE

Posted 30 October 2014 - 07:00 AM

So... you're not using a tool, just generating insert statements? ( Because, you know, most tools would fix that for you. )

There is a way to set the default date format for the session. Something like:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
-- or, perhaps, in your case
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD- HH24:MI:SS.SSSS';
-- or something like that; I don't have an Oracle instance to hand


Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 592
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: CONVERTING SQL TO ORACLE DATE ISSUE

Posted 30 October 2014 - 07:21 AM

I am using the Oracle sql developer migration tool that is built into Oracle sql developer
Was This Post Helpful? 0
  • +
  • -

#4 Sheepings  Icon User is offline

  • D.I.C Addict

Reputation: 73
  • View blog
  • Posts: 571
  • Joined: 05-December 13

Re: CONVERTING SQL TO ORACLE DATE ISSUE

Posted 30 October 2014 - 07:55 AM

That won't help you. The syntax that is used in the Oracle is not exactly the same as what is used in SQL or MySQL from what I recall. So you'd be best to do this manually. To my knowledge, there is no easy way to do it apart from using a cheat sheet. Maybe this might shed some light on some of what you're doing DarenR.

To be clear, are you looking to convert from two server types (oracle or ex: MySQl) or are you looking for a conversion for the date insert statement?

This post has been edited by Sheepings: 30 October 2014 - 07:57 AM

Was This Post Helpful? 0
  • +
  • -

#5 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 592
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: CONVERTING SQL TO ORACLE DATE ISSUE

Posted 30 October 2014 - 08:04 AM

what i have to do is take an entire database and move it to oracle . the database is 3gig in sql server over 50 tables all of which have different date variations in sql server. I am trying to use the migration tool supplied in oracle sql developer to move all the data over. The only issue i am having is with dates all other data looks like it worked.-- this is running online so no scripts are being generated.
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: CONVERTING SQL TO ORACLE DATE ISSUE

Posted 30 October 2014 - 12:20 PM

Oracle's tools pretty much only do Oracle. Migration here means one oracle instance/schema to another.

Curiously, while the same can generally be said of Microsoft's tools, when it comes to SQL Server this is not the case. While Oracle DB links are strictly Oracle, MS DB links actually leverage OLE DB on the server and can connect to pretty much anything.

This post shows going the wrong direction, but should give you a place to start: http://dbadiary.word...rver-with-ssms/

Generally, you create a native schema to dump into first. The canned tools will try to do this, but they often suck at it. You can use them to start and clean up, if you like. Once you have a table in the receiving database that roughly matches a table in the source database: fire.

As a programmer, I often write scripts for this. Most languages will do some kind of translation from source DB types to native and from native to destination DB. If the DB types are different, but similar, then it really doesn't matter. I prefer F# or C# for this kind of thing, but Python can do it easily enough. Even PHP. Anything with an abstraction layer and support for both RDBMS providers in question.
Was This Post Helpful? 0
  • +
  • -

#7 Sheepings  Icon User is offline

  • D.I.C Addict

Reputation: 73
  • View blog
  • Posts: 571
  • Joined: 05-December 13

Re: CONVERTING SQL TO ORACLE DATE ISSUE

Posted 30 October 2014 - 03:30 PM

DarenR I wrote a whole thing out on this earlier, but It cleared the post and never posted. (For future reference, copy your answer before submitting.) Lesson learned...

Anyway, what you can do is create a script to execute on the tables containing a date time format, and use a select statement to select from your current table and then create a new connection to your Oracle server to insert the results you selected from each row, inserting them into your new DB with the correct query string formats. I had wrote a basic script out in C# earlier but it was lost in the post, and sorry but i don't have time to write it again.

The difference with the fields is SQL and Oracle. Which look like Oracle: ('2014/10/30:10:20:09AM', 'yyyy/mm/dd:hh:mi:ssam')); and SQL which is likely: ('20141030 10:20:09'); Simply: YYYY-MM-DD HH:MM:SS As I think i said before, the variations are similar but very different thus making it harder to do this in bulk with ease. Your best bet is a C# App; loop each field for the date table in SQL and select the variables, and re-insert them with the new query string format into the Oracle database. That is the only way I can think to do it using a SQLReader and simply execute the returned values to execute the query into your Oracle Database instead.

My other post earlier today would have been much more helpful. But I hope this helps some.

This post has been edited by Sheepings: 30 October 2014 - 03:39 PM

Was This Post Helpful? 0
  • +
  • -

#8 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 592
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: CONVERTING SQL TO ORACLE DATE ISSUE

Posted 31 October 2014 - 04:18 AM

After doing a lot and I mean a lot of trial and error i think i have it figured out. Simple update statements did not work nor did trying to change data types of the columns. I had to go into oracle sql developer and look at almost every option until i found tools > preferences > database > nls
in the format sections for date and time i set them as follows
YYYY.MM.DD HH24:MI:SS
YYYY.MM.DD HH24:MI:SSXFF


if you dont change it there it will ask for the MON or name of the month not the number of the month and will error out everytime.

btw do not use oracle forums they are a bunch of douche bags

This post has been edited by DarenR: 31 October 2014 - 04:18 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1