Setting the Default Date Format

The default date format is specified in the NLS_DATE_FORMAT database parameter. Your DBA can change the setting of NLS_DATE_FORMAT by setting this parameter s value in the database s init.ora or spfile.ora file, which is read when the database is started. Your DBA can also set NLS_DATE_FORMAT using an ALTER SYSTEM command.

You can also set the NLS_DATE_FORMAT parameter for your own session using SQL*Plus. You do this using ALTER SESSION .

Note  

A session is started when you connect to a database and is ended when you disconnect.

For example, the following ALTER SESSION statement sets NLS_DATE_FORMAT to MONTHDDYYYY using SQL*Plus:

 SQL>  ALTER SESSION SET NLS_DATE_FORMAT = 'MONTH-DD-YYYY';  Session altered 

You can see the use of this new date format in the output from the following query that retrieves the dob column for customer #1:

  SELECT dob   FROM customers   WHERE customer_id = 1;  DOB ----------------- JANUARY  -01-1965 

You may also use the new date format when inserting a row in the database. For example, the following INSERT statement adds a new row to the customers table. Notice the use of the format MONTH-DD-YYYY when supplying the dob column s value:

 INSERT INTO customers (   customer_id, first_name, last_name, dob, phone ) VALUES (   6, 'Fred', 'Brown', 'MARCH-15-1970', '800-555-1215' ); 

If you then disconnect from the database and connect again as the store user , you ll find that the date format is back to the default. That s because any changes you make using the ALTER SESSION statement only last for that particular session: when you disconnect, you lose the change.