Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 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
Solution Description
NLS_DATE_FORMAT="MM/DD/YYYY" -- or whatever date format you need
2) Also, this parameter needs to be changed from the registry:
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:
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
"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 - 08:14:38 CST