Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unanswered: Oracle 9i - is there any datetime data types

    hi! i am currently doing database migration from informix DB to oracle DB. i found difficulty in changing the datetime data from informix to oracle.

    eg,
    ------------------------------------------------------------------
    informix oracle
    ------------------------------------------------------------------
    datetime year to second date
    datetime hour to second date
    ...
    ------------------------------------------------------------------

    instead of using only "date" data type, is there any other way?
    because if using "date", i need to make a lot of changes in my program codings as below,
    1) when insert records into a table, i need to add the syntax in my every single codings.
    insert into table1 values(to_date(date1,'YYYY/MM/DD HH24:MIS'));

    2) when display the record from a table,
    SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MIS') "Date" FROM date_table

    honestly, there are a lot need to touch up. so really need your help, is there any other ways? thank you very much!



    regards,
    jason

  2. #2
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi Jason,
    As such there is not datatype in Oracle which support u as you want. There is one more datatype, TIMESTAMP but it will again will not do good for you.

    Here, i may like to ask one thing: How you are doing database Migration from Informix to Oracle..?? Are you doing it manually by taking out data in some way from iinformix and inserting it into Oracle.

    We, at our organization was using Sybase Database 11.0.3.3. on HP-UX and we have successfully migrated from Sybase to Oracle on SUN SPARC Solaris-9. We are having Oracle9iR-2 9.2.0.4 database on SUN. And even we had the same issue of date datatype conversion because is Sybasew we had datetime or smalldatetime.

    So, there is a tool called Oracle Migration Work Bench (OMWB) which is provided by Oracle and is free download. You may try to use this OMWB and it will give you very simple path.

    It will automatically generate the migration script & even if yoy want it can created a oracle schama for you. It does lot many thing regading database migration...but...yiu have to really see some more points and you have to verify it for ur correctness.

    Regarding seeing the date in proper format, you may put a database trigger at logon which sets the date format in DD/MM/YYYY HH24:MIS for the sesion and whenever any date is selected you will get by default the date in DD/MM/YYYY HH24:mi:ss format.

    hope, it will able to help u. in case if u need any more information regarding OMWB, i will be glad to provide u the same.

    Regards,
    Kamesh Rasotgi
    - KR

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    change NLS_DATE_FORMAT to the format you want and you are all set.

    here is an example, keep in mind I only set this for the session,
    but you might want to set it permanently:
    PHP Code:
    platform@kod1create table a (the_date date);

    Table created.

    platform@kod1insert into a values (sysdate);

    1 row created.

    platform@kod1select from a;

    THE_DATE
    =========
    12-MAY-04

    platform
    @kod1alter session set nls_date_format 'DD/MM/YYYY HH24:MI:SS';

    Session altered.

    platform@kod1select from a;

    THE_DATE
    ===================
    12/05/2004 09:13:00

    platform
    @kod1insert into a values ('12/05/2004 09:13:00');

    1 row created.

    platform@kod1commit;

    Commit complete
    Last edited by The_Duck; 05-12-04 at 10:17.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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