tigermoth
asked on
Oracle SQL Plus - changing date format
Hello,
when creating a table with a "date" field, how do i change the format of the date (to DD-MM-YYYY)??
the code I've got at the moment is as follows:
Create table sales
(SALEDATE date(DD-MM-YYYY))
any ideas how i'm supposed to do this? (it doesn't like the above code..)
when creating a table with a "date" field, how do i change the format of the date (to DD-MM-YYYY)??
the code I've got at the moment is as follows:
Create table sales
(SALEDATE date(DD-MM-YYYY))
any ideas how i'm supposed to do this? (it doesn't like the above code..)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQL> create table sales( SALEDATE date );
Table created.
SQL> alter session set nls_date_format = 'dd/mm/rr';
Session altered.
SQL> insert into sales values ( '29/03/04' );
1 row created.
SQL> select SALEDATE, to_char(SALEDATE,'dd-mon-y
SALEDATE TO_CHAR(SALEDATE,'
-------- -----------
29/03/04 29-MAR-2004
If you dont issue ALTER session, when issueing SELECT, the result is:
29-Mar-04
You also can change the date format at database level by change the value of nls_date_format parameter in the init.ora.
Hope this helps