DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: HS date conversion error

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    HS date conversion error

    Hi,

    I have successfully copied a table from foxpro db using the ODBC HS connection which I followed from the docs.

    The sample command was:

    create table EMP as select * from emp@foxpro_db;

    I found some puzzling result on the date fields though
    when I compared the 2 tables (oracle and foxpro).

    foxpro db:

    use emp
    select bday from emp

    bday
    ----
    12/05/51
    02/05/96
    10/19/95
    05/21/01
    04/10/00

    On Oracle DB:

    select bday from emp;

    bday
    ----
    05-DEC-51
    05-FEB-96
    19-OCT-95
    21-MAY-01
    10-APR-00

    which I presumed will be the same result as above (foxpro result). but
    when i display it using to_char(bday,'mm-dd-yyyy ')

    select to_char(bday,'mm-dd-yyyy ') from emp;

    bday
    ----
    02-05-9696
    10-19-9995
    12-05-2051
    05-21-3001
    04-10-2200

    The month and day seemed ok but the year were not, i cant even see
    a pattern how much did it added to it.

    Any idea plsssss.....thanks

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Each DBMS use specific storage format for dates.

    You should have used date format in the create statement.

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Can you describe the table emp?
    ---------------

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    you might need to pre-create the table and use date conversion during data copying
    ---------------

  5. #5
    Join Date
    Feb 2007
    Posts
    212
    hi hunnies... the bday in emp is in DATE datatype format the HS assigned it
    automatically....what defference does it make if I precreate it as DATE.

    how do I display the 4-digit year value in foxpro? It might be that
    the year value really was the same as shown in the oracle display
    Last edited by jennifer2007; 02-28-2007 at 05:18 AM.

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    create table tabname (f1 datatype, bday date)

    insert into tabname select f1, to_date(bday,'MM/DD/YY') from tabname2@foxpro
    ---------------

Posting Permissions

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


Click Here to Expand Forum to Full Width