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..)
tigermothAsked:
Who is Participating?
 
musduCommented:
Hi,

date datatype has an internal format. You should give a date format when you selecting records. e.g;

SELECT to_char(mydate, 'DD-MM-YYYY') from mytable;

regards.
0
 
baonguyen1Commented:
tigermoth,  the default format of date datatype is either DD-MON-YY or from version  8.1.6 and up it is DD-MON-RR. You can not change the date format as your post but you can change it at the session level using ALTER SESSION command. Let say we create a table like this:

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-yyyy') from sales;

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.