Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle SQL Plus - changing date format

Posted on 2004-03-29
2
3,825 Views
Last Modified: 2008-02-01
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..)
0
Comment
Question by:tigermoth
2 Comments
 
LVL 6

Accepted Solution

by:
musdu earned 20 total points
ID: 10703990
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
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10705048
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question