date format not accepted???

[GoSooJJ]

hi all!

first, thank you for your help on previously I asked ^^.
Now, i have an another problem. I had no problem with update or insert date format like 'mm/dd/yyyy' into access database. Now I moved to oracle and getting error that "not a valid month" when i try to update or insert date format as 'mm/dd/yyyy'. I found that Oracle date format is 'dd-Mon-yy'. But my database date format is 'mm/dd/yyyy' already. What should I do? I want to keep the original date format since the client is familiar w/ this.

Please help.
Thank you!

JJ //

 

[ghilpert]

You may need to set or create the string value NLS_DATE_FORMAT registry entry to MM/DD/YYYY
This is in HKEY_LOCAL_MACHINE\SOFTWARE
on the client PC.

 

[GoSooJJ]

ghilpert,
thank you for help.

ok, which computer should i change? i have a oracle server seperate box. if my workstation has to change then this will not be what i was looking for. well, i will change the setting on server first ^^.

thx again.

 

[sem]

In fact Oracle stores dates in its internal format. This setting is used by applications for implicit convertions of dates to strings and vice versa. This means that you have to change this setting on client side. You may set this value on session level by ALTER SESSION or make explicit to_date() conversion.

 

[carp]

If you want the default date format to be MM/DD/YYYY for ALL sessions that use the database, you can set NLS_DATE_FORMAT='MM/DD/YYYY' in your init.ora file and bounce the instance.

 

[MFGlobalDBA]

In the United States, Oracle still comes with a default date format of 'dd-Mon-yy', why they do that is beyond me, but they don't do that in other countries like Canada. It is set to the ISO (International Organization on Standardization) format of 'YYYY-MM-DD'. Yeah for Oracle Canada!

Anyway, you should change your init.ora to include and entry to set the default date format to something that is Y2K compliant, whatever format you choose. Previous posts have shown how to do this.

That is all well and good except when someone comes in via the client (SQL*Net or Net8). In that case, there are setting on your machine that override the default database format. You want to look at the HKEY_LOCAL_MACHINE\SOFTWARE
and you also want to check out your "glogin.sql" script that resides with your tnsnames.ora (in the same directory, so something like c:\Ora81\network\admin\glogin.sql) and be sure the date format is not set to something else within this script, because this script overrides everything else.

If you can't find where the date is being set wrong, you can simply add an oracle statement to this glogin.sql to force the default date format to be the way you want. Here is an example :

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

To test this, just go into SQL*Plus and select SYSDATE from Dual and see what format is returned.

Hope this helps.