Re: Date formatting and Oracle 9i database...

"ARP" <sirapitt_at_hotmail.com> wrote in message news:<tqvq487tjb032b_at_corp.supernews.com>...
> Need help with date formatting and Oracle 9i database...
>
> I have an Oracle 9i database and have followed the documented instructions
> for setting the National Language date format as follows:
>
> 1. Added the string NLS_DATE_FORMAT = "MM/DD/YYYY" to the initialization
> file for my database.
>
> 2. Stopped and restarted the database, then checked the list of applied
> parameters in the Enterprise Manager to ensure that new parm is present.
> ----------------------------------------------------------
>
> When I query against a date column in my database [e.g. "select
> TO_CHAR(EVAL_DATE) from struct_tbl;"], I get the default date format being
> returned [i.e. "31-DEC-00"].
>
> What else do I need to do to get a date format of MM/DD/YYYY returned from
> my query when I use the TO_CHAR() function?
>
> NOTE: Using the extended version TO_CHAR(EVAL_DATE, 'MM/DD/YYYY') is out of
> the question since our software is used in many different geographical
> regions where date formats differ and I cannot therefore specify a date
> format in the program code.
>
> Thanks for any help possible.
>
> Austin Pitt.

set the env var NLS_DATE_FORMAT at the client side (Unix) or the registry var NLS_DATE_FORMAT in HKLM\software\oracle to MM/DD/YYYY Your note is debatable. Just because of this reason you should never ever rely on a default format, and better use to_date(....,<date format>) everywhere.
I once had to deal with a create table statement where the developers had been using their own defaults in the default clause, ie startdate date default '31-dec-00'. However, the database defaults were different. When it came to reorganizing the database, the affected table being a table with many foreign keys pointing to it, that was definitely *NOT* funny.
Just a war story as food for thought!

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Tue Sep 25 2001 - 02:33:15 CDT