Solved

Oracle SQL Plus - changing date format

Posted on 2004-03-29
2
3,803 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Subquery Modification 4 113
sql query help 7 82
What is ISQL? 6 101
Compare and update data between two databases 6 42
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now