Date datatype 
Author Message
 Date datatype

Hi,

I am doing manual migration from MSSQL 7.0 to a Oracle 8i database (the beta
migration workbench tool kept spewing JREW errors ..)

I am aware of some datatype incompatibilities from the migration workbench
doc, but it does not talk indepth about how to handle date/time types.

I am using ASP scripts to insert records via ODBC from the SQL database to
the Oracle database. I have a DATE column in a table but it kept saying
error not a valid month. Specifically, I need to insert datetime records of
the format MM/DD/YY HH:MM:SS AM eg. "11/29/99 5:31:25 PM". Oracle, even in
SQLPlus, does not accept this.

Am I missing anything?

Does anyone have any experience doing the migration this way? Any heads-up
would be really appreciated! :)



Wed, 18 Jun 1902 08:00:00 GMT
 Date datatype

Quote:

>I am using ASP scripts to insert records via ODBC from the SQL database to
>the Oracle database. I have a DATE column in a table but it kept saying
>error not a valid month. Specifically, I need to insert datetime records of
>the format MM/DD/YY HH:MM:SS AM eg. "11/29/99 5:31:25 PM". Oracle, even in
>SQLPlus, does not accept this.

>Am I missing anything?

The TO_DATE function or the NLS_DATE_FORMAT.

By default Oracle's date format is DD-MON-YY I think. If you want to insert
a different format, you either need to use the TO_DATE conversion function
or use an ALTER SESSION SET NLS_DATE_FORMAT command to change the default
format for that session. Of course, you can also change the INITsid.ORA file
and specify a new default format for that instance, but I would rather leave
that option alone.

TO_DATE should be the easiest in your case I think, e.g.
INSERT INTO foo
( mydate )
VALUES
( TO_DATE('11/29/99 5:31:25 PM', 'MM/DD/YY HH:MI:SS AM') )

regards,
Billy



Wed, 18 Jun 1902 08:00:00 GMT
 Date datatype
Just spent the better part of the day looking for a solution to this. Here's
it to share .. need to make changes to the registry on NT to effect the date
format:

Problem Description:
====================

User needs to change default NLS_DATE_FORMAT on Windows NT server.

Or user has set the NLS_DATE_FORMAT in the init.ora on the server, but on
the
Windows, Windows NT, or Windows 95 client application the dates are not
still
not as described in the NLS_DATE_FORMAT in the init.ora in the server.

Related Terms
=============
NLS
date
format
SQL*Plus
OPO
sqlplus
ODBC
registry
regedt32
regedit

Solution Description
====================

1) The NLS_DATE_FORMAT parameter needs to be added to database
initialization
file (for example, initorcl.ora for default instance ORCL):

       NLS_DATE_FORMAT="MM/DD/YYYY"  -- or whatever date format you need

2) Also, this parameter needs to be changed from the registry:

   - run regedt32

   - select HKEY_LOCAL_MACHINE
         Software
             ORACLE

   - select 'Edit' from menu
   - select 'Add Value...' from submenu

        Value Name = NLS_DATE_FORMAT
        Data Type = REG_EXPAND_SZ or REG_SZ
        String = MM/DD/YYYY (or whatever date format that you need -- no
                             quotes are necessary)

3) To change NLS_DATE_FORMAT for a 16-bit application on the server, the
value
must be entered in the server's ORACLE.INI:

       NLS_DATE_FORMAT=MM/DD/YYYY

4) You must then stop the database and services, and restart.

5) NLS_DATE_FORMAT needs to be set on any client which has NLS_LANG set (for
example, all Windows, Windows NT, Windows 95, and OS/2 clients), because
there
is a default NLS_DATE_FORMAT implied for any NLS_LANG.

For 16-bit client applications on Windows, Windows NT, and Windows 95, the
parameter needs to be set in ORACLE.INI.  For 32-bit applications on Windows
95, the parameter needs to be set in the registry:

   - run regedit.exe

   - select HKEY_LOCAL_MACHINE
         Software
             ORACLE

   - select 'Edit' from menu
   - select 'New' from submenu
   - select 'String Value' from submenu

        Value Name = NLS_DATE_FORMAT
              Data = MM/DD/YYYY (or whatever date format that you need -- no
                             quotes are necessary)

For 32-bit applications on Windows NT clients, the parameter is set as in
step 2, above.

Reference
==========
See PrSol:2061538.102 to set NLS_DATE_FORMAT for multiple Oracle Homes



Quote:
> Hi,

> I am doing manual migration from MSSQL 7.0 to a Oracle 8i database (the
beta
> migration workbench tool kept spewing JREW errors ..)

> I am aware of some datatype incompatibilities from the migration workbench
> doc, but it does not talk indepth about how to handle date/time types.

> I am using ASP scripts to insert records via ODBC from the SQL database to
> the Oracle database. I have a DATE column in a table but it kept saying
> error not a valid month. Specifically, I need to insert datetime records
of
> the format MM/DD/YY HH:MM:SS AM eg. "11/29/99 5:31:25 PM". Oracle, even in
> SQLPlus, does not accept this.

> Am I missing anything?

> Does anyone have any experience doing the migration this way? Any heads-up
> would be really appreciated! :)



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Technique for - Date datatype into mm/yy - SQL SERVER 2K

2. Date datatype problem

3. Date Datatype in SQL server and Crystal Rpt

4. Date datatype?

5. Help Converting Field to Date Datatype

6. Query on formatting date datatype

7. Date datatype

8. Date Datatype

9. Just Date datatype

10. DATE datatype = NULL?

11. (DATE)datatype problems

12. saving date datatype


 
Powered by phpBB® Forum Software