Re: HELP! Default date format in SQL*Plus isn't dd-mon-yy!

From: Andrew Allen <andrew.allen_at_handleman.com>
Date: Wed, 26 Feb 2003 16:09:21 GMT
Message-ID: <3E5CD9A8.1030004@handleman.com>

R wrote:
> Hi!
>
> I'm in a bind here. Doing some schoolwork and for some reason the default
> date format produced by SQL*Plus is not the expected dd-mon-year, but
> YY-MM-DD. I've tried altering the settings in my Date/Time control panel
> with no success.
>
> This wouldn't be a big issue except when I create my tables using the given
> script, the dates are corrupted, since they are in the default format and
> not the expected format.
>
> Can anyone tell me what to enter in Oracle's SQL*Plus client to change the
> default date?
>
> I'm running this on a Windows XP machine with the Oracle 9i personal edition
> installed.

OK, since you admit you are a student I will point you in the proper direction and leave it up to you to find the correct solution(s).

1. Oracle stores dates in an internal seven byte date format.
2. Oracle displays dates in a default format, as you listed (yy-mm-rr).
3. You can change the default display format, as well as comma/point for 

numbers using NLS settings -- I will leave it up to you to find what these are and how to set them system wide and at the session level. 4. NEVER - NEVER - NEVER ( I cannot say it enough ) NEVER rely on default date masks (format). ALWAYS use an explicit TO_DATE() or TO_CHAR() function to store or display dates. This way you never have to worry about the default date display format.

It is probably good that you have been bitten by this bit of ( reliance on defalt ) foolihsness while still learning -- perhaps it will help lead you into good programming habits.

BTW, the date/time settings on your PeeCee have NOTHING to do with SQL*Plus and oracle display defaults.

--
AjA

Received on Wed Feb 26 2003 - 10:09:21 CST