date format in sql plus.

Posted on 2003-10-29
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.
Question by:jheff
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
LVL 12

Expert Comment

ID: 9640810

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

Expert Comment

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


Accepted Solution

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

Expert Comment

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 ?

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

688 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