[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle SQL Plus - changing date format

Posted on 2004-03-29
2
Medium Priority
?
3,855 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 80 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

656 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