Solved

Oracle SQL Plus - changing date format

Posted on 2004-03-29
2
3,838 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
[X]
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
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

724 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