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

    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.

    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!


  2. #2
    Join Date
    Apr 2003
    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 on HP-UX and we have successfully migrated from Sybase to Oracle on SUN SPARC Solaris-9. We are having Oracle9iR-2 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.

    Kamesh Rasotgi
    - KR

  3. #3
    Join Date
    Jul 2003
    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;


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

    Session altered.

    platform@kod1select from a;

    12/05/2004 09:13:00

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

    1 row created.


    Commit complete
    Last edited by The_Duck; 05-12-04 at 09: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