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.