Solved

date format in sql plus.

Posted on 2003-10-29
4
40,865 Views
Last Modified: 2007-12-19
i want to change the format of a date using the function:TO_CHAR OR TO_DATE  to this format:
from dd-mm-yyyy
to: month, date(th) yyyy
ex: january, seventeenth 2003.
0
Comment
Question by:jheff
4 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9640810

select to_char(sysdate,'fmMonth')||','||to_char(sysdate,'fmDDspth')||' '||to_char(sysdate,'YYYY') from dual
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 9640914
Even though catchmeifuwant is correct, let me point out the case sensitivity issue with formatting dates:

to_char(sysdate, 'fmMonth') = January
to_char(sysdate, 'fmmonth') = january
to_char(sysdate, 'fmMONTH') = JANUARY

to_char(sysdate, fmDDspth') = SEVENTEENTH
to_char(sysdate, fm'Ddspth') = Seventeenth
to_char(sysdate, fm'ddspth') = seventeenth


0
 
LVL 7

Accepted Solution

by:
grim_toaster earned 50 total points
ID: 9649417
Or you can do it all in one to_char call:

SELECT TO_CHAR(SYSDATE, 'fmMonth, fmDdspth YYYY') FROM dual
0
 

Expert Comment

by:NadirMaroof
ID: 10289836
i want to add date in a column of my table in "DD/Mon/YYYY" format, what sort of check should i introduce to ensure this behavior ?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

733 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