Re: Date datatype

From: Chi Sim Tang <chisim_at_amoeba.com.sg>
Date: 1999/11/29
Message-ID: <81u1oe$5nn$1@violet.singnet.com.sg>#1/1

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

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

"Chi Sim Tang" <chisim_at_amoeba.com.sg> wrote in message news:81thto$190$1_at_mawar.singnet.com.sg...
> 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! :)
>
>
>
>
>
Received on Mon Nov 29 1999 - 00:00:00 CST