Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2007
    Posts
    21

    Unanswered: MySQL Data to Oracle date issue

    Hello,
    I'm trying to migrate all of my MySQL data over to my new Oracle database and I'm having a problem with my date-time column. It's as if Oracle does not like the standard SQL date-time format ("YYYY-MM-DD HH:MMD") that comes from MySQL.

    I have half a million rows to insert. What should I do?

    Thanks,
    tom

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It's as if Oracle does not like the standard SQL date-time format
    I move data regularly between MYSQL & Oracle; including DATE datatypes.
    Without know exactly what you are doing wrong it is hard to say how to fix the problem.
    You'll need to use TO_CHAR to properly format Oracle dates as strings that MYSQL accepts.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    'YYYY-MM-DD' is a standard? Not where I come from.

    How do you insert those records? Using some tool? Direct INSERT INTO statements? Basically, you should apply the TO_DATE function and do something like this:
    Code:
    SQL> -- In my database, default date format is 'dd.mm.yy':
    SQL> select sysdate from dual;
    
    SYSDATE
    --------
    01.12.07
    
    SQL> create table t_mysql (date_column date);
    
    Table created.
    
    SQL> -- Now, let's insert date in format you use WITHOUT any help:
    SQL> insert into t_mysql (date_column) values ('2007-12-01 19:22');
    insert into t_mysql (date_column) values ('2007-12-01 19:22')
                                              *
    ERROR at line 1:
    ORA-01861: literal does not match format string
    
    
    SQL> -- Didn't work, right? OK, let's include the TO_DATE function and
    SQL> -- apply it to the string we tried to insert:
    SQL> insert into t_mysql (date_column)
      2    values (to_date('2007-12-01 19:22', 'yyyy-mm-dd hh24:mi'));
    
    1 row created.
    Now let's see what we've done:
    Code:
    SQL> select * from t_mysql;
    
    DATE_COL
    --------
    01.12.07
    
    SQL> -- To select a full date + time format, I can either use the TO_CHAR
    SQL> -- function, or alter session and set different date format:
    SQL> select to_char(date_column, 'dd-mon-yy hh24:mi') from t_mysql;
    
    TO_CHAR(DATE_CO
    ---------------
    01-pro-07 19:22
    
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select * From t_mysql;
    
    DATE_COLUMN
    -------------------
    01.12.2007 19:22:00
    
    SQL>
    What should you do? I don't know, at least not until you say HOW you are migrating those records. However, I hope those several SQL*Plus lines will be of some help to you.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Littlefoot
    'YYYY-MM-DD' is a standard? Not where I come from.

    ...

    TO_CHAR(DATE_CO
    ---------------
    01-pro-07 19:22
    oh? what planet has a month called provember?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Posts
    21
    Thanks, the insert data is in a sql file which looks like this but with thousands of rows.

    INSERT INTO INSTPTCH(patch, name, domain, idate, ldguid)
    VALUES ("JavaForMacOSX10.4Release5-5.0", "tool", NULL, "2007-05-01 10:14:23", "2BD4D68A-DFB4-4087-83DF-FDDBDE8AE6A4"),
    ("SecUpd2007-004Ti-1.0", "tool", NULL, "2007-05-01 10:14:23", "2BD4D68A-DFB4-4087-83DF-FDDBDE8AE6A4"),
    ("Java131and142Release2-2.0", "tiger", NULL, "2007-05-02 17:46:05", "F3354323-F6E0-4753-8E16-D38D2D7F7A26"),
    ("iTunesPhoneDriver-1.0", "tiger", NULL, "2007-05-02 17:46:05", "F3354323-F6E0-4753-8E16-D38D2D7F7A26"),
    ("iTunesX-7.1.1", "tiger", NULL, "2007-05-02 17:46:05", "F3354323-F6E0-4753-8E16-D38D2D7F7A26");

    I'm using AquaData Studio to do the inserts but I have no problem using sqlplus if that's a suggestion.

    Thanks,
    tom

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    alter session set nls_date_format='YYYY-MM-DD HH24:MI.SS'
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by r937
    oh? what planet has a month called provember?
    LOL!!! It is 'prosinac' (i.e. December in Croatian).

  8. #8
    Join Date
    Dec 2007
    Posts
    21
    I tried setting the nls_date_format and then retried the inserts and I get "ORA-01843: not a valid month".

    is the date format not sticking?

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I tried setting the nls_date_format and then retried the inserts and I get "ORA-01843: not a valid month".

    Please realize that we are NOT mind readers & we are NOT standing behind you to see what you really are doing.

    Unless & until you use CUT & PASTE (plus <code tags>), we have no idea what is actually occurring.

    >is the date format not sticking?
    May be you should be using Super Glue.

    I am willing to wager that the problem is PEBKAC induced (& resolved).

    Code:
    sqlplus
    
    SQL*Plus: Release 10.2.0.2.0 - Production on Sun Dec 2 11:42:48 2007
    
    Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
    
    Enter user-name: dbadmin
    Enter password: 
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> create table ID10t (my_date_time date);
    
    Table created.
    
    SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI.SS';
    
    Session altered.
    
    SQL> insert into ID10T values ('2007-12-31 23:59:59');
    
    1 row created.
    
    SQL> select * from ID10T;
    
    MY_DATE_TIME
    -------------------
    2007-12-31 23:59.59
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Dec 2007
    Posts
    21
    I do appreciate the help, but there is no reason to be rude. I was doing a copy and paste and I was still getting the error. I have since resolved it.

    Thanks,
    tom

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I have since resolved it.
    You reached out to others for assistance, yet you unwilling to share YOUR solution with those who might have a similar problem in the future.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Littlefoot
    'YYYY-MM-DD' is a standard?
    Yes, ISO standard

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Thank you, Shammat! I'd expect that ISO, as a "serious" organization, explains things strictly. It is a nicely (friendly ) written article.

    So it is ISO 8601; fine. I guess I could live with it, although my whole life it was DD.MM.YYYY. (note full-stops after every part of a date). Format that confuses me the most is MM.DD.YYYY (or one of its derivations); I just can't get used to put 'days' between 'months' and 'years', so when I see something like '04.06.2007' I don't know is it April or June. Therefore, it would really be OK if we all stick to a standard (even though I don't like it, but hey! I might not like SELECT statement syntax, but am forced to use it "as is").

    I apologize for being such an ignorant and thank you all who pointed that out.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Littlefoot
    Thank you, Shammat! I'd expect that ISO, as a "serious" organization, explains things strictly. It is a nicely (friendly ) written article.
    The link I posted is just a FAQ. They charge for the "real" document describing the standard (which I assume is a lot more detailed)

    I guess I could live with it, although my whole life it was DD.MM.YYYY. (note full-stops after every part of a date).
    Same here in Germany

    Format that confuses me the most is MM.DD.YYYY (or one of its derivations); I just can't get used to put 'days' between 'months' and 'years', so when I see something like '04.06.2007' I don't know is it April or June.
    Yes, I feel the same (and from my point of view it's not logical to first state the month then the day).
    But I guess that derives from the fact that the people in the US (don't know about UK) do speak that way (June 4th), so I guess the writing simply reflects the spoken language - as it does it does in Germany too.
    That's why I like the ISO standard when putting dates into files. When you start with YYYY it's pretty clear that the next "part" will be the month - at least I have never ever seen someone using YYYY-DD-MM...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •