Re: century in date field?

In article <31r53m$hki_at_homer.cs.mcgill.ca>, jennifer_at_cca.qc.ca (Jennifer Waywell) writes:
|> I'm an historian with a pc-database background, new to the world of
|> Oracle. It seems to me that the answer to this question should be easy,
|> but I haven't found anything in the documentation...
|>
|> Is it possible to alter the defaults and allow a date field to accept a
|> date with the century (ie '14-JAN-1672') or must you make it a CHAR field
|> and use the TO_DATE function each time that you want to do a calculation
|> involving the date?
|>
|> Thanks,
|>
|> --
|> | Jennifer Waywell |
|> | Canadian Centre for Architecture |
|> | Montreal, Quebec |

Couple of ways. I will assume that you are using Oracle7, if not than neither of these apply. There is an init.ora parameter called NLS_DATE_FORMAT. Set this to the desired default format, in your case 'DD-MON-YYYY'. This will change the default format for the ENTIRE DATABASE. All applications. If you just want to change it for your session (until you disconnect) use

SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL> select sysdate from dual;

SYSDATE

Table created.
SQL> insert into ttt values ('14-JAN-1672');

1 row created.

SQL> select * from ttt;

F1